How can I count the results ???
 

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

How can I count the results ???
 

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


"Mark Allen" wrote:

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

How can I count the results ???
 

=sumproduct((a2:a22="Sue")*(b2:b22="Apples"))
--
Don Guillett
SalesAid Software

"Mark Allen" <Mark wrote in message
...
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

How can I count the results ???
 

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

see also help files on Arrays...

"Mark Allen" wrote:

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

How can I count the results ???
 

Spot on thanks.....

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

"Ron Coderre" wrote:

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


"Mark Allen" wrote:

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