Questions

Nested Countif Question

Locked

Nested Countif Question

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?

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:

Another way to get at what you want is to use a pivot table...

Use COUNTIFS in Excel 2007

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

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:

Another way to get at what you want is to use a pivot table...

Use COUNTIFS in Excel 2007

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