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

have a look in help index for COUNTIF

--
Don Guillett
SalesAid Software

"Colin Thornton" wrote in message
...
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

Do you expect a result of 2 from your example?

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=516581

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




"Colin Thornton" wrote:

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

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=516581