Nesting countif functions

[Evan]

Can anyone help on how to properly write a function to count the number of
cells in a column with a certain value if they have a corresponding value in
a different column? For example, I want to count how many times "BIG"
appears in Column B, if the same row has "B" in Column A.
A B
1 B BIG
2 C BIG
3 C SMALL
4 D SMALL
5 B SMALL
6 F MEDIUM
7 F BIG
8 E SMALL
9 E MEDIUM
10 D BIG
11 C BIG
12 B SMALL
13 C MEDIUM
14 E SMALL
15 A SMALL
16 F MEDIUM
17 D SMALL
18 A BIG
19 B SMALL
20 C BIG

Thanks!
Evan

 

[Rick Rothstein \(MVP - VB\)]

Try this formula...

=SUMPRODUCT((A1:A100="B")*(B1:B100="BIG"))

The top end of the ranges (A100, B100) can be made to cover any span you may
have now or in the future.

Rick