Can someone help me formulate the correct syntax for a nested countif question?
I have a report where I need totals from multiple columns that match certain criteria.
Question Background:
Column D asks a simple yes/no question of if a the line item equipment has been completed or not.
"Has the equipment been upgraded?" - Yes/No
Column L indicates which of the two locations the equipment is from. California and Arizona are the two locations.
"What location is the unit in?" CA or AR
There are 206 units in Arizona and 460 in California
So basically I need a countif that will first count the number of units from column L that match California, but that also have a matching criteria of "Yes" in Column D
Any suggestions?
- Follow via:
- RSS
- Email Alert
Question
0
Votes
Nested Countif Question
Updated - 9th Jan 2008
Answers (1)
0
Votes
Kludge...
You're essentially dealing with 4 possible cases of outcomes:
1. Y CA
2. N CA
3. Y AZ
4. N AZ
Instead of trying to use COUNTIF, a quick-and-dirty kludge is to add a calculation column to assign one of these cases to each row of data using a nested IF statement -- then run your COUNTIF off that column using labels to designate the "human" version of the case.
Example:
Assume:
Column A = Upgrade Status (Y or N)
Column B = Location of Units (CA or AZ)
Column C formula:
=IF(A2="y",IF(B2="ca",1,3),IF(B2="ca",2,4))
Count Cases:
=countif(C1:C#,1) [CA units upgraded]
=countif(C1:C#,2) [CA units not upgraded]
=countif(C1:C#,3) [AZ units upgraded]
=countif(C1:C#,4) [AZ units not upgraded]
Another way to get at what you want is to use a pivot table...
1. Y CA
2. N CA
3. Y AZ
4. N AZ
Instead of trying to use COUNTIF, a quick-and-dirty kludge is to add a calculation column to assign one of these cases to each row of data using a nested IF statement -- then run your COUNTIF off that column using labels to designate the "human" version of the case.
Example:
Assume:
Column A = Upgrade Status (Y or N)
Column B = Location of Units (CA or AZ)
Column C formula:
=IF(A2="y",IF(B2="ca",1,3),IF(B2="ca",2,4))
Count Cases:
=countif(C1:C#,1) [CA units upgraded]
=countif(C1:C#,2) [CA units not upgraded]
=countif(C1:C#,3) [AZ units upgraded]
=countif(C1:C#,4) [AZ units not upgraded]
Another way to get at what you want is to use a pivot table...
11th Jan 2008
Replies
If you have Excel 2007, one of the new functions that has been added is
=COUNTIFS() - You now have an unlimited number of possible searches, filters, logicals to get the Count that you are looking for.
Danny Rocks
www.thecompanyrocks.com
=COUNTIFS() - You now have an unlimited number of possible searches, filters, logicals to get the Count that you are looking for.
Danny Rocks
www.thecompanyrocks.com
danny@...
9th Apr 2009
