# Nested SUM & COUNTIF Formula With #DIV/0! Error

Mar 16, 2008
I have a student grade worksheet with this formula and it returns # DIV/0 which I need to solve the problem. I have tried many different ways and none seem to work. Con someone assist with this problem. Here is the formula:

= SUM(R13,R55,R97,R139,R181)/(COUNTIF(R13,">0")+(COUNTIF(R55,">0")+(COUNTIF(R97,">0")+(COUNTIF(R139,">0")+(COUNTIF(R181,">0"))))))

View 9 Replies
ADVERTISEMENT
Mar 6, 2013

It's actually a sumif/countif that works fine if i sort my table, which i don't want to do.

Here's the original formula:

Code:

(SUMIF(H4:H35,"1",G4:G35)/COUNTIF(H4:H35,"1")*1+SUMIF(H4:H35,"2",G4:G35)

/COUNTIF(H4:H35,"2")*2+SUMIF(H4:H35,"3",G4:G35)

/COUNTIF(H4:H35,"3")*3+SUMIF(H4:H35,"4",G4:G35)

/COUNTIF(H4:H35,"4")*4)/10

I'm trying to apply the formula using some other filters, like if(C4:C35=O5 & I4:I35=M5).

I tried creating a SUMPRODUCT out of it but it was just as bad:

Code:

=(SUMPRODUCT((C4:C35=$O$5)*(I4:I35=$M$5)*(H4:H35=1)*(G4:G35))

/SUMPRODUCT((C4:C35=$O$5)*(H4:H35=1)*(I4:I35=$M$5)*1)*1+...

And also this with sum(if)

Code:

=(SUM(IF(C4:C35=$O$5,IF(I4:I35=$M$5,IF(H4:H35=1,G4:G35))))

/COUNT(IF(C4:C35=$O$5,IF(I4:I35=$M$5,IF(H4:H35=1,G4:G35)))))*1+...

Is there a shorter formula that I'm not seeing ? I'm also confronted with the need of adding an "ISERROR" in the formula because not all ranges have the 1-4 numbers in column H.

View 9 Replies
View Related
Dec 4, 2013

I'm logging failed password attempts and need to find the latest time logged for a single user (when user has failed 5 times).

So the 5th attempt, I need that time

So the usual - Application.UserName in the next free cell in a range and time stamp in the next cell.

What I've tried is:

Say log range is A1:B500 and

A=Username, B=Time stamp

In column C I've nested the two together

(this has more code but not required to get this working)

In C1

Code:

=A1&" "&"live"&" "&B1

So this will bring back "Username live 23:12:12". Now, say there is 5 of these (all with different times)

How do I find the latest time for that user?

Code:

IF(COUNTIF("USERNAME LIVE"&"*")=5,[find the latest time in that list",""

Just to wind you up some more, other users name will be logged and muddled up so it's literally -

"Excel - if a user has failed 5 times then give me the time of the 5th attempt"

I've tried a mixture of COUNTIF, MAX, LARGE, TIMEVALUE(...)

View 4 Replies
View Related
Mar 16, 2007

I searched on this and didn't find what I was looking for. I want to count entries that have critieria I specify in two different ranges. Is countif the way to do this?

View 9 Replies
View Related
Aug 28, 2007

I just need it to be able to test two seperate conditions on two seperate columns but i cant work out how its done

for example, i have two columns on a sheet, one called type and one area, both are text, i need to count how many items of one type are in a particular area. so if i have two columns "area" and "type" i want a total count of how many rows contain area="north" and type="b"

View 13 Replies
View Related
Oct 8, 2009

Hi guys, hopefully you can shed some light onto this small problem I am having.

The current formula I'm trying to use is:

=IF(VLOOKUP(A4,EPG1!D4:AN26,6,FALSE),COUNTIF(PASS,""))+IF(VLOOKUP(A4,EPG1!D4:AN26,6,FALSE),COUNTIF(F AIL,""))

This isn't producing the desired results.

What I am trying to do in words is this:

IF... in this array it equals A4 then look at coloum 6 and then Countif col 6 says Pass and then add another if it says Fail....

View 7 Replies
View Related
Apr 13, 2007

I'm working on a formula that will determine the lowest number in a range.

The complication is that the range varies based on how many rows match a certain criteria.

The formula I have created works correctly, however, I need to have the range go up to 10, and because of the limit of 7 nested IF's, I have to stop at 6.

=IF(A2=A1,"",IF(COUNTIF($A:$A,A2)=1,G2,IF(COUNTIF($A:$A,A2)=2,MIN(G2:G3),IF(COUNTIF($A:$A,A2)=3,MIN(G2:G4),IF(COUNTIF($A:$A,A2)=4,MI N(G2:G5),IF(COUNTIF($A:$A,A2)=5,MIN(G2:G6),IF(COUNTIF($A:$A,A2)=6,MIN(G2:G7),"")))))))

After a result is returned from this formula, I have to do the almost exact same formula, based on the min value from these results.

View 9 Replies
View Related
Jan 27, 2010

I am entering the following Nested IF(AND statement into a cell and getting a message stating "You've entered too many arguments for this function." Any idea on what formula I can use to get the needed information into this cell based on the fact that there are different inputs that can be entered into the related cell that will trigger the needed respons in the working cell?

=IF(AND(K1051="Down",L1051<2),"Yes","",IF(AND(K1051="Partially Down",L1051<2),"Yes",""))

View 2 Replies
View Related
Jun 10, 2014

I am trying to produce a report of supplier transactions sorted by area code. I have a spreadsheet of data consisting of Supplier Code, Supplier Name, Transaction Amount and Area Code.

I want to be able to firstly seperate the transactions by area, then also consolidate the data so it shows one row per supplier with total amount spent on that supplier and a transaction count on that supplier. I know how to sumif the transaction total and countif the transaction count.

However I have problems consolidating the suppliers in to one row per unique supplier and I also have problems nesting an AND statement in to the sumif/countif statements. I need additional criteria in the forumula to only count transactions in a specified area code.

I've attached an example spreadsheet to make it alot clearer. See results sheet in this workbook.

View 3 Replies
View Related
Nov 26, 2008

I am trying to track metrics for a QC dept. Right now my sheet has 35 lines of data and a header row, for 36 lines total. This is my funtion that isn't quite right:

View 2 Replies
View Related
May 27, 2012

I am struck in using IF /AND in nested function as follows,

A1B1C1D1E1F1G1

1 3 5 7 9 32

=IF(AND(F2>G2,AND(IF(A2>1,A2,AND(IF(C2>5,C2,AND(IF(E2>9,E2),""))

(Above, A1 to >G1 are cell address , in exact rows are actual values)

View 9 Replies
View Related
Nov 5, 2007

I am receiving an error message for a nested if statement. Excel is telling me my statement contains too many variables.

I am using a VLOOKUP to look up data in a table based upon a result in the first table, however, the entry that is returned is different based upon a unique cell value.

Would anyone have any suggestions on how to shorten this down?

=IF(CR="AG",IF(ISBLANK(A7),"",IF(ISNA(VLOOKUP(A7,SI.xls!$A:$G,3,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,3,FALSE))),

IF(CR="G",IF(ISBLANK(A7),"",VLOOKUP(ISNA(VLOOKUP(A7,SI.xls!$A:$G,4,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,4,FALSE))),

IF(CR="MG",IF(ISBLANK(A7),"",VLOOKUP(ISNA(VLOOKUP(A7,SI.xls!$A:$G,5,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,5,FALSE))),

IF(CR="C",IF(ISBLANK(A7),"",VLOOKUP(ISNA(VLOOKUP(A7,SI.xls!$A:$G,6,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,6,FALSE)))),

IF(CR="UC",IF(ISBLANK(A7),"",VLOOKUP(ISNA(VLOOKUP(A7,SI.xls!$A:$G,7,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,7,FALSE)))))))

View 9 Replies
View Related
Jul 17, 2012

I am using VBA to put a formula into a cell. The formula is

VB: = IFERROR(VLOOKUP(LEFT(A5,11), 'New Quarterly Report'!A$11:E$37,3,FALSE), " - ")

But when I try to put this into a cell using the value option, I get a run time error 13: Type Mismatch. No problems while im compiling it but it comes up only during execution

VB: Sheets("Summation Table Template").range("B4").Value = "= IFERROR(VLOOKUP(LEFT(A5,11),'New Quarterly Report'!A$11:E$37,3,FALSE), " - ")"

When I try to put only the VLOOKUP, it works fine.

VB: Sheets("Summation Table Template").range("B4").Value = "= VLOOKUP(LEFT(A5,11),'New Quarterly Report'!A$11:E$37,3,FALSE)"

But I want the cell to not show #N/A when a value through vlookup is not found. Other formulas Iv tried are to use ISERROR with IF and also to store the formaula in a string and then put it into Range.Value but it still gives me a run time error 13.

View 2 Replies
View Related
Aug 27, 2008

I have a Userform which has 2 textboxes. User enter his employee id on textbox1 then enters a valid break time code which are 1,2 and 3 on textbox2. If User typed 1 then first breaktime start time will be pasted on the worksheet. When User returns after break he then enters his employee id again and then type 1 to end his first breaktime, the end time will be pasted on the worksheet. My program works fine however, I am trying to place a code which will prompt the user to end his first break time before entering another valid break code. Kindly check my code below as I am receiving an error message. 'Run-time error 9' Subscript out of range. This thing is driving me nuts. Any advise or can you tweak my coding vba experts.

View 14 Replies
View Related
Oct 15, 2011

Using Excel 2010.

I extracted data from .pdf to Excel using Able2Extract. Now I need to scrub the output a bit.

I see commonalities in the data for the start and stop of each set of data that I can key in on.

Once I find the start and stop points for each set of data I would like to fill all rows in-between the points and then discard anything that remains outside of these boundaries.

I have something wrong in my logic, way too many rows are deleted.

Code:

Option Explicit

Sub GetLineSets()

'Purpose: Identify relevant line sets, delete all other rows

Dim wbBook As Workbook

Dim wsData As Worksheet

Dim strFormula As String

Dim lngRows As Long

Dim C As Range

Dim blnFlag As Boolean

[Code] ......

View 4 Replies
View Related
Jul 6, 2012

Is there a way to count cells that show up with the "Not Available Error", or even better, is there a formula that will count all cells in range range, except those with the "Not Available Error".

View 5 Replies
View Related
Jul 15, 2004

I am trying to do a countif in another workbook.

I enter my formula:

=COUNTIF('path[filename]tab'!$AH$11:$AH$300,"yes")

So this formula works fine when I have the other workbook open, but the next day when I open up the file (with the formula in it) and I click "update" button on the "this workbook contains links to other data sources" popup I get a #VALUE! error in the cell. Does anyone know how to correct this?

As soon as I open up the linked workbook the error goes away, but I didn't think this is how its supposed to function. I thought you didn't have to open up any linked documents. All the documents are on my desktop, so its not a share drive issue.

To test it out, I tried doing just a simple link to cell A1 in the other workbook and that works fine, so I'm thinking its the COUNTIF function that causing the problem.

View 9 Replies
View Related
Aug 24, 2014

I am trying to use a combination of Concatenate and IF formula to produce an email.

My input.

D4: First Name:

D5: Middle Name:

D6: Last Name:

D8: Organization:

Once all these are filled, I want the formula to produce a result like

FirstName.M.LastName@Organization.com

I have used the following formula.

[Code] .....

Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.

FirstName..LastName@Organization.com

How do I remove the additional (.) in cases where there is no middle name.

Attached File : Email Generator.xlsx

View 3 Replies
View Related
Mar 24, 2007

I've been testing the countif function and for some reason it isn't working for me.

I have the following text in cells A1:A3

"apple"

"banana"

"peach"

I have the following text in cells C1:C3

"apricot"

"banana"

"grape"

I put the following formula in B1 and copied down into B2 and B3.

=COUNTIF($C$1:$C$3,A1)

It is meant to count how many of each fruit in A1:A3 is also in C1:C3.

The banana is the only one that is in both lists so the formula in B2 should return 1.

But all three formula's return zero.

If I change the range in the countif formula to relative it works. eg., =COUNTIF(C1:C3,A1)

View 3 Replies
View Related
Feb 20, 2009

I have a rather large formula that I put together and it's shown in column 'C' in the small sample file I have attached. I'd like to add one more criteria such that I avoid duplicate solutions in column 'C'. i.e. I only want the first occurrence of a group.

The formula works fine 'bucketing' the first entry. If I have an solution in C I want the formula to not give me another answer if the cell immediately above has an answer. In other words, rows 11 and 15 would be blank because rows 10 and 14 have an answer.

I've tried inserting another criteria in the AND statement, like ISBLANK for the cell above, but I can't get it to work. What am I doing wrong, OR is there a simpler solution?

View 2 Replies
View Related
Sep 17, 2009

=IF(C2="Windows",B2,IF((AND(A2=A3,C3="Windows")),B3,IF((AND(A3=A4,C4="Windows")),B4,IF((AND(A4=A5,C5="Windows")),B5,IF((AND(A5=A6,C6="Windows")),B6,IF((AND(A6=A7,C7="Windows")),B7,IF((AND(A7=A8,C8="Windows")),B8,"None")))))))Here is a link to the spreadsheet (D2 being the formula cell):

http://img199.imageshack.us/img199/8500/29072008.jpg

Basically, I am trying to put the columns data (if it has a porch, conservatory, windows) into one row for the contract number, as the dump which I have, is giving me seperate row's instead of one.

I am trying to get the formula to check if the contract number matches on the following row, and if it does, to check if the Product Description matches the header of that row (if it's a window, conservatory) if so, copy the quantity cell into D1, however, it's copying it even if the contract numbers do not match and I do not know where I am going wrong!!

View 9 Replies
View Related
Feb 20, 2009

Objective: To find out which customers order certain items and which customers dont order certain items. Many customers may order the same item eg customer A, B, C, D all order item "4567"

I have 2 worksheets.

Worksheet 1: Showing 30 item codes, item description and customers. Items in col A (A2:A31), description in col B (B2:B31) runnning down vertically. Customer name in cell 1 of all other columns running across horizontally, eg C1, D1, E1.... (C1:GF1). There are 186 customers. (A formula needs to start at C3 and dragged to GF3)

Worksheet 2: Raw data showing customers in column A and items in column B, There are 3,753 rows. Customer in column A are duplicated as the same customer may order a number of items so for eg

ABC PTY LTD 5671

ABC PTY LTD 5683

ABC PTY LTD 5692

ABC PTY LTD 5610

ABC PTY LTD 5611

Tried the below formula

=if(C1='S2'!A2),=if('S1'!A2='S2'!B2),"ordered","")

View 8 Replies
View Related
Apr 1, 2009

I have a spreadsheet which contains dates (amongst other info) of when a specific form was completed for a client. We have 'date windows' which these should fall into. I'm really struggling with how to work this one out and whether to use nested IF's or try some kind of code (although my vb knowledge kind of stops after recording a macro). Bascially I need to know for each 'date window' if the window has been met or missed. There may be up to 30 different dates per person (in my example sheet I have up to 9).

View 2 Replies
View Related
Aug 7, 2009

He works at state tax administration. He had this formula at work to calculate income tax.

View 3 Replies
View Related
Aug 20, 2008

I currently use 3 different formulas to return a value I want. The first one is =LEN to return a value. I then want to decuct 1 from this length value so I just use =A2-1 in the next cell. I then use =right so my returned result is always the X right most characters -1. Is there a way I can nest all these formulas so I'm getting the desired result in just one cell?

View 3 Replies
View Related
Nov 18, 2012

where the issue with the following formula is?

=IF(AND($E$2="N",$AA$550),"BT1",IF(AND(Z5="F",Y5>=0),"CLEAR",IF($AA$5

View 2 Replies
View Related
Jul 23, 2013

I have to write a formula which states the following:

if cells AA1,AB1 &AC1 = 0 then "Slow-Moving",

if of these cells AA1,AB1 &AC1 contains a number then "OK",

if cells, AA1,AB1,AC1,Z1,X1,Y1 all = 0 then "Non-Moving"

I believe an If and AND combination could work but its not working for me.

View 8 Replies
View Related
Nov 1, 2006

I have the following IF formula. It is working fine except the underlined part; it does not returned the value 15 ...

View 4 Replies
View Related
Feb 4, 2008

I have searched the threads high and low, but no one seems to have come across this issue: My formula works fine untill it comes across a blank in Comparison!E16: =IF(AND(Comparison!E14=Comparison!E15,Comparison!E15=Comparison!E16),"",IF(AND(Comparison!E14=Comparison!E15,Comparison!E16=""),"") IF(AND(Comparison!E14<>Comparison!E15),Comparison!E14))

I am comparing cells from a previous sheet onto Comparison so that I can see where the discrepancies lie.

I want to:

return nothing if all the cells match;

return nothing if the first two match, but the third is blank;

return the value if the first cell isn't matching

keeping in mind that this is only for the top cell: Comparison!14 - I will then have to bring it down to 15 and 16

View 6 Replies
View Related
Mar 3, 2014

If the value in the Discount Y/N column is equal to Y, the formula should check if the value in the Tenure (Yrs) column is less than 5.

View 3 Replies
View Related