Counting multiple criteria

[KCR]

Hello

I am trying to count a combination of criteria across a visitor information
log.

For example I am trying to count how many times a NEW visitor with LUNG
cancer attended a BOOKED activity. E.g. from the below I would want to be
able to end up with the answer 2:

New or Return Cancer Type Activity

New Lung Drop In
Return Skin Booked
New Lung Booked
New Skin Drop In
New Lung Booked

Can anyone help? I hope this make sense!

Many thanks
KCR

 

[Don Guillett]

=sumproduct((a2:a22="new")*(b2:b22="lung"))

 

[Teethless mama]

=SUMPRODUCT(--(A1:A5="New"),--(B1:B5="Lung"),--(C1:C5="Booked"))

 

[KCR]

Brilliant. Thank you very kindly! K

=SUMPRODUCT(--(A1:A5="New"),--(B1:B5="Lung"),--(C1:C5="Booked"))

 

[Teethless mama]

You're Welcome!

Brilliant. Thank you very kindly! K

 

[Ashish Mathur]

Hi,

you can also use the following array formula Ctrl+Shift+Enter

=sum(if((range1="New")*(range2="Lung")*(range3='Drop In"),1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Peo Sjoblom]

You can shorten that to

=SUM((range1="New")*(range2="Lung")*(range3="Drop In"))


--


Regards,


Peo Sjoblom