COUNTIF Multiple Values

R

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
 
R

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)
 
R

Risky Dave

Perfik!

Many thanks

Ron Coderre said:
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)
 
R

Ron Coderre

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

Regards,

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top