COUNTIF Multiple Values

[Risky Dave]

Hi,

I have two columns of fixed, equal, size (A1:A100 and B1:B100).
Both columns are completely populated by formulae returning values from
other parts of the workbook, These values are text not numeric. Where there
is no value, the cell is empty (other than the formula, of course). It is not
possible for A to be empty and B not to be emptty and vice versa.

The whole thing looks something like:

A B
1 Apple Pie
2
3 Pear Juice
4 Apple Pie
..
..
100 Apple Juice

What I want to do is count the number of each variation and output it in a
table:
Pie Juice
Apple 2 1
Pear 0 1

This seems to requre ANDing two COUNTIF statements, along the lines of:

=AND(countif(A1:A100,"apple"),countif(B1:B100,"Pie")

With each cell of the table having a custom variation of this formula. The
AND statement is returning a TRUE or FALSE, so how do I count the number of
Apple Pies, Apple Juices, Pear Pies etc?

TIA

Dave

 

[Ron Coderre]

Try this:

With your posted set up...

F1: Pie
G1: Juice

E2: Apple
E3: Pear

F2: =SUMPRODUCT(--($A$2:$A$100=$E2),--($B$2:$B$100=F$1))
Copy F2 across and down through G3

Alternatively, you could just use a pivot table which would automatically
create the structure you're looking for

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Risky Dave]

Perfik!

Many thanks

Try this:

With your posted set up...

F1: Pie
G1: Juice

E2: Apple
E3: Pear

F2: =SUMPRODUCT(--($A$2:$A$100=$E2),--($B$2:$B$100=F$1))
Copy F2 across and down through G3

Alternatively, you could just use a pivot table which would automatically
create the structure you're looking for

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Ron Coderre]

You're very welcome....I'm glad I could help.

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)