Questions

Nested Countif Question

+
0 Votes
Locked

Nested Countif Question

CA_Countryboy
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?
  • +
    0 Votes
    casachs

    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...

    +
    0 Votes
    danny

    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

  • +
    0 Votes
    casachs

    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...

    +
    0 Votes
    danny

    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