Count Items Based on Another Column

How can you count items in one column, based on a criterion in a different column? We’ve shipped orders to the East region, and want to know how many orders had problems (a problem note is entered in column D). COUNTIF sounds like the right function to use, but it doesn’t work for this problem.

Count Items Based on Another Column http://blog.contextures.com/

Easy to Sum Based on Criteria

If we want to get a total quantity for the East region orders, it’s easy to do with the SUMIF function. We can check region names in column B, then sum the amounts in column C.

=SUMIF(B2:B11,G5,C2:C11)

countifscolumn02

COUNTIF Function

Unfortunately, the COUNTIF function doesn’t work the same way. (I don’t know why!) The COUNTIF function only has 2 arguments — you select a range to check, and the criterion to look for.

So, the best that we can do with COUNTIF is to get a count of East region orders.

=COUNTIF(B2:B11,G5)

countifscolumn01

More Criteria With COUNTIFS

Fortunately, there is a COUNTIFS function, and we can use it to get a count based on multiple criteria. We’ll check column B for “East” region entries, and check column D for cells that are not empty.

The criterion “<>” is the “not equal to” operator. Used alone it means “not equal to ‘no text'”, so it will count cells that are not empty.

=COUNTIFS(B2:B11,G5,D2:D11,”<>”)

countifscolumn03

Get the Problem Count

With this COUNTIFS formula, the result of the Problems count for the East region is 2. Rows 2 and 9 have orders for the East region, and an entry in the Problems column.

countifscolumn04

Blank <> Empty

In this example, the notes were typed in the Problem column, and the remaining cells were empty. Our formula only counts the cells that are not empty.

However, if column D contained formulas, and some cells had a result of “” (an empty string), those cells would be also counted as “not empty”, just like the cells that contain text, even though they look blank.

Be sure that your blank cells are really empty, if you’re going to use this formula. Otherwise, you could use a SUMPRODUCT formula, like this one:

=SUMPRODUCT((B2:B11=”East”),(D2:D11<>””))

NOTE: Those are two minus signs before each section of the SUMPRODUCT formula, not long dashes.

Download the Sample File

To see more ways to count in Excel, you can visit the Excel Count Function page on my Contextures website, and download the sample file.

The zipped file is in xlsx format, and does not contain macros.

___________

Count Items Based on Another Column http://blog.contextures.com/

You may also like...

9 Responses

  1. MF says:

    Oh, I didn’t know we can use “” directly… I used to use “”””.

    btw, we may also revise the COUNTIFS formula a bit to include the blank “”
    =COUNTIFS(B2:B11,G5,D2:D11,”?*)

  2. RP says:

    If you want to use COUNTIFS and are not sure that blank cells are really blank even if they look blank this will work and not count cells where the result of a formula is “” .
    =COUNTIFS(B2:B11,G5,D2:D11,”>”””)

  3. Lap says:

    If I have same 3 digit code in more than 5 cells then it should give me the count. Pls help

    • @Lap, if your 3 letter code is in cell F1, put this formula in another cell, such as cell G1. It will count the values in A1:A100

      =IF(COUNTIF(A1:A100,”*” & F1 & “*”)>3,COUNTIF(A1:A100,”*” & F1 & “*”),””)

  4. Jo says:

    So I looked through your examples above, and I think I am close to finding a formula that has evaded me for some time; but I just can’t seem to figure it out. If you could help me out, YOU WOULD SOAR TO THE TOP OF MY CHRISTMAS LIST!!

    Here is an example of my columns. I am trying to count the number of dates (occurrences) in Column C for anyone in Column A whose cell contains “< 1 YR". I realize that I would have to use "*< 1 YR*", but based on the criteria below, the formula should generate an answer of 3, but what is the formula to count the occurrences?

    COLUMN A COLUMN C

    RN 01/01/2015
    RN / < 1 YR 02/10/2004
    CLIN TRANS 09/06/2018
    NA 01/10/2000
    HUC / < 1 YR
    RN 01/01/2000
    < 1 YR 08/13/2018
    HOUSE
    HOUSE / < 1 YR 06/20/2001

  5. muhammad sajid says:

    very heplfull

  6. Gabriel Ortiz says:

    Excelent information! I had a time looking for an quivalent to the SUMIF function to count values. This article was very helpfull. Tanks a lot

  7. Ian says:

    Problem with #N/A
    ~~~~~~~~~~~~~~~~
    SUMPRODUCT worked great – once I realised I needed to remove any #N/A (from a Vlookup result, which was later pasted as a value)
    I Replaced all #N/A with N/A.
    Then the formula worked perfectly.
    Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *