How can I count the results ???

[Guest]

ok the excel loks like this

A1 A2
Sue Apples
Chris Apples
John Pears
Sue Apples
Chris Apples

How do I write a formula to give me the answer to how many Apples has Sue
got ??

The answer is two but what is the formula ???????

Any help would be welcome

 

[Guest]

With

Your posted data in cells A1:B5

Try this:
D1: Sue
E1: Apples
This formula returns the count of Sue/Apples combinations
F1: =SUMPRODUCT((A1:A5=D1)*(B1:B5=E1))

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Don Guillett]

=sumproduct((a2:a22="Sue")*(b2:b22="Apples"))

 

[Guest]

try this..
=SUM((A1:A5=C1)*(B1:B5=D1)) and hit control-shift-enter

see also help files on Arrays...

 

[Guest]

Spot on thanks.....

I had been trying that but I was using the whole column...