formula for occurrences

[James__S]

Hi

Is there a formula which can count occurrences?

In cell AM5 is there a formula which counts groups or single entries a 1.....example the answer for AM5 is 5....E5:F5 = 1 occurrence, K5 = 1 occurrence, M5 = 1 occurrence, R5:S5 = 1 occurrence and M5 = 1 occurrence ...The count is for both Sick and Carers occurrences
Does the persons name also need to be in the formula?

I will copy the formula down to AM100

Thanks

JS

[AlKey]

Try this

=SUM(COUNTIF(D5:AL5,"*"&{"Sick","Carers"}&"*"))

[gmr4evr1]

Try this in AM5

Please Login or Register  to view this content.

[James__S]

Hi

1. It gives the answer as 8 instead of 5....
2. It gives the answer as 5 but does not count Carers

Thanks