Counting multiple criteria
 

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

Counting multiple criteria
 

=sumproduct((a2:a22="new")*(b2:b22="lung"))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

Counting multiple criteria
 

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


"KCR" wrote:

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

Counting multiple criteria
 

Brilliant. Thank you very kindly! K

"Teethless mama" wrote:

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


"KCR" wrote:

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

Counting multiple criteria
 

You're Welcome!

"KCR" wrote:

Brilliant. Thank you very kindly! K

"Teethless mama" wrote:

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


"KCR" wrote:

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

Counting multiple criteria
 

Hi,

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

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

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

Counting multiple criteria
 

You can shorten that to

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


--


Regards,


Peo Sjoblom



"Ashish Mathur" wrote in message
...
Hi,

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

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

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