Counting rows using multiple criteria in Excel

Often in Excel you have a big fat table of data that you need to perform various analyses with. It wouldn't be out of the bounds of reasonability that you would want to count the number of rows that have a certain set criteria. Luckily, Excel provides the COUNTIF function to do exactly that. But it has severe limitations.

COUNTIF is really only good if you are only bothered about using a single criteria to identify what rows to count. For example, taking the sheet illustrated on the left it is simple to count how many people are below 35 years old by using the formula

=COUNTIF(B2:B6,"<35")

which returns the number 3. The COUNTIF function has two parameters; the first is the range of data to look at, the second is the criteria of the data you want to count - in this case anyone who is less than 35 years old.

But what if you are interested in using multiple criteria? For instance, how do we count how many people are below 35 years old and earn less than £20,000? Not so simple. COUNTIF only works with a single criterion.

To do this, we can use array formulae. They are a lesser known type of Excel beast, because for some reason or other they are not ever so well documented and promoted by the help files et al. They are however a) powerful and b) letting you do things you otherwise couldn't without a lot more effort.

An array formula can work on multiple ranges simultaneously. These ranges must be the same size and shape however, as each number in the first range range must be able to correspond with a number in the second range and vice versa. Also, to add a spot of extra crypticness to the surroundings, after you have typed your array formula in you must press Ctrl + Shift + Enter to save it in the cell, instead of just the normal Enter. You'll know if you did it correctly, because Excel will automatically put curly braces around the whole formula. If it doesn't, you did it wrong. Do not try and add the braces manually yourself because that will not work.

A simple array formula might be that used to add up the products of two numbers. For instance, say you wanted to know the total area (height multiplied by width) of a set of rectangle shapes. You could work out the area of each one separately and add it up, or you could use the following array formula on the data to the right.

{=SUM(A2:A4*B2:B4)

(not forgetting that you don't put the curly braces in, Excel does it for you. That formula would give the answer 29. What it is doing is to work down the range A2 to A4 and applying the multiply function (*) to that cell and its counterpart in the second range B2 to B4. To spell it out further, the sum it does is (A2*B2) + (A3*B3) + (A4*B4).

Interesting yes, but how does this help us count data with multiple criteria? Firstly remember that multiplying anything by zero gives you zero. With that in mind, here is a way to count how many people are below 35 years old and earn less than £20,000:

{=SUM((IF(B2:B6<35,1,0)*(IF(C2:C6<20000,1,0))))}

giving you the answer 2.

To talk through what the formula is doing, first start with the IF function. IF takes an expression as its first parameter, and returns the second parameter if that expression is true, and the third if not. Therefore =IF(B2<35,1,0) returns 1, because cell B2 has a value less than 35 in it.

Likewise =IF(C2<20000,1,0) also returns 1, as C2 has a value less than 20,000 in it.

So the array function goes down the range B2 to B6 translating it into either 1 or 0, and multiplying it by its equivalent in C2 to C6 with the same translation. If both B-whatever is less than 35 and C-whatever is less than 20000, this becomes the sum 1 * 1 which gives you an answer of 1. If either of the criteria are not met, the sum is either 0 * 0, 0* 1 or 1 * 0, all of which give you 0.

The SUM at the beginning of the equation simply adds up these ones and zeros, which effectively gives you the count of how many entries in your table match both the criteria. Now won't that make you look like an Excel genius?

If you want more information on Array formulae and possible uses, Microsoft has a mini-tutorial and several examples.

Addendum: You can also write and read Excel array formulae using Visual Basic for Applications (VBA) code, although at first it may not seem obvious how. You need to specifically access (get or set) the property "FormulaArray" of the range object. Do not put the curly braces in your code, again Excel will see to these automatically. For instance, you could programatically insert the 2-criteria formula above into whichever spreadsheet cell is currently active via the following code:

ActiveCell.FormulaArray = "=SUM((IF(B2:B6<35,1,0)*(IF(C2:C6<20000,1,0))))"


Comments

Excel Ranking

Anyone know how to rank a value where the reference range spans across multiple worksheets? Thanks.

Re: Excel ranking

Hi,

There is a reasonable way to do that if you can engineer it such that the reference range spans the same cells in each of the multiple sheets. Start by defining a 3-D named range covering the cells in question. To do this, use the Insert menu, then Name, then Define. Create a new name – e.g. "datasource", which refers to the appropriate range.

A 3D-range can be written in the format =Data1:Data2!$A$2:$A$6 for example, referring to cells A2-A6 on both sheets Data1 and Data2 – which is why the data has to span the same cells on both sheets.

Once you have defined this range, you can then use it as the second parameter of the =RANK function. For example, =RANK(A2,datasource) to see the rank of cell A2's value compared to the multi-sheet range.

Hope that makes some kind of sense, let me know if not and I’ll try to make it clearer!

?

?

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd> <blockquote> <del>
  • Lines and paragraphs break automatically.
  • You may post code using <code>...</code> (generic) or <?php ... ?> (highlighted PHP) tags.
  • You may use [acidfree:xx] tags to display acidfree videos or images inline.
  • Images can be added to this post.

More information about formatting options

Captcha
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
4 + 7 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.