In a previous post I covered counting how often a value occurs within a range. In that post we looked into one criteria, and we used the COUNTIF function.

But what if we want to count cells that meet multiple criteria. We can not use the COUNTIF function in this case, so we will take a different approach and use the SUM function.

In the example below we want to count how often “John” appeared in the list in quarter 2 (Q2). i.e. our critera is “John” in column B and “Q2″ in column C. We will use the following formula for this purpose:

=SUM((B1:B17=”John”)*(C1:C17=”Q2″))

However this formula should be an “Array formula”. To make it an “Array formula” you should press CTRL+SHIFT+ENTER while you are in the edit mode of the formula. It should then look like this :

{=SUM((B1:B17=”John”)*(C1:C17=”Q2″))}

This formula will give us a result of 3 as you can see.