Yesterday, my friend Alan posed a problem. He wanted to count the number of rows that met criteria in two separate columns. He was frustrated that COUNTIF wouldn't allow him to do that.

Since Excel 2007, there have been a couple of functions that have allowed you to check against multiple criteria being satisfied: COUNTIFS and SUMIFS.


COUNTIFS is exactly the same as COUNTIF, except that instead of checking against one range, it checks against many. All of the conditions must be true for the entry to be counted.

For example, in a list of employees you may want to count all people in the New York office earning over $50,000. Or in a risks and issues register, you may want to count all risks with a high likelihood and high impact.

The syntax is as follows:


So you basically list the ranges contain the things that you want to check, each followed by the condition that you want to check for. And as long as the areas match up, you're golden.

=COUNTIFS(A:A,"New York",B:B,">=50000")

This will count all rows in which column A contains the value "New York" and column B contains a value in excess of 50,000. Note that any check other than an exact match must be encased in double quotes.


This behaves in much the same way. And the syntax is only subtly different.


You merely plonk your sum range in front of your list of ranges and criteria.

=SUMIFS(C:C,A:A,"New York",B:B,">=50000")

If stock options are housed in column C, then the above formula will total the stock options for all New York employees earning over $50,000.

This entry was posted in Formulae explained and tagged , , , . Bookmark the permalink.

2 Responses to COUNTIFS and SUMIFS

  1. Denny Bohs says:

    Hi I am working on a multi-tab workbook and am wanting to count a value each time it appears in a specific range on each tab. For instance each tab represents a game and the batting order is in the same range on each tab. I want to count the number of times each player batted in each slot in the bating order. I am using the following formula, but keep getting an error. $D2 is the player name on the summary tab; the player name is listed in column AC on each game tab; batting order number is listed in column AD on each game tab.


  2. Luis Costa says:

    How can I use SUMIFS to sum exact criteria values?
    For example if I had in the criteria range1 values as 0055 and 00055 when I set the criteria as "0055" the Sumifs sum all values that begins with "0055".

Comments are closed.