Counting Data

C

Colin Thornton

I am trying to count the number of occurrences that data in two columns
match

A B C D E
1

2 Colin Yes SE

3 Sue Outside Mid

4 Mary Inside SE

5 Julie Yes SE

6 Mark Yes Mid

7 Fiona Outside SE

For example i want to count the number of times "Yes" appears along side
"SE"


Hope you can help.

Colin
 
D

daddylonglegs

Do you expect a result of 2 from your example?

=SUMPRODUCT(--(B2:B7="Yes"),--(C2:C7="SE"))
 
A

Ashish Mathur

Hi,

Although the sumproduct is a better formula to use, my array formula
(Ctrl+Shift+Enter) is an alternative. Assuming your data is laid out like
this:

Yes Smith
No Mark
Yes Smith

In cell C8, array enter (Ctrl+Shift+Enter) the following formula:

SUM(IF((B4:B6="Yes")*(C4:C6="Smith"),1,0))
 
D

daddylonglegs

Hi Ashish, of course SUM with CTRL+SHIFT+ENTER is an alternative but you
don't really need the IF in this case

=SUM((B4:B6="Yes")*(C4:C6="Smith"))

would suffice
 
Top