| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| counting multiple criteria | Excel Worksheet Functions | |||
| Counting Multiple Criteria | Excel Worksheet Functions | |||
| counting using multiple criteria | Excel Discussion (Misc queries) | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Linear Mode
