Count Your Excel Records Based on Multiple Conditions
Have you ever wanted a quick count of the number of records
in your Excel worksheet that meet a set of conditions? Use
an array formula. You create array formulas the same way
that you create other formulas, except that you press
CTRL+SHIFT+ENTER to enter the formula.
Let's look at an example. Say you're running a produce
department and you want to analyze your inventory to find
which items cost more than 25¢ and have a total inventory of
two items.
Your current inventory looks like this.
A
B
C
Banana
0.25
2
Pear
0.25
2
Orange
0.33
3
Grape
0.5
4
Prune
0.5
5
Apple
0.25
3
Lime
0.33
2
Lemon
0.5
4
Kiwi
0.5
4
Peach
0.25
3
A1:A10 is the product name
B1:B10 is the product price
C1:C10 is the number on the shelf
Here's the array formula you'd use:
1.
In the cell where you want the results type: =SUM(IF($B$1:$B$10
> .25, IF($C$1:$C$10=2,1,0)))
2.
Press CTRL+SHIFT+ ENTER.
This formula checks column B for values greater than .25
and, for each record meeting that condition, checks column C
for values that equal 2. Then it adds all the records that
meet both conditions.
In the example given, the result is 1.
|