Count occurance of multiple text criteria
 

Im using the formula

=SUM(LEN(B2:B955)-LEN(SUBSTITUTE(B2:B955,"Pension","")))/LEN("Pension")

This works fine to count the number of occurances for the word Pension but
Im stuck when trying to count using multiple criteria eg the the Pension and
the word Scheme.

Any help is greatly appreciated.

Sam.D

Count occurance of multiple text criteria
 

Try

=SUM(COUNTIF(B2:B955,{"*Pension*","*Scheme*"}))

OR for whole cell match
=SUM(COUNTIF(B2:B955,{"Pension","Scheme"}))


--
Jacob


"Sam.D" wrote:

Im using the formula

=SUM(LEN(B2:B955)-LEN(SUBSTITUTE(B2:B955,"Pension","")))/LEN("Pension")

This works fine to count the number of occurances for the word Pension but
Im stuck when trying to count using multiple criteria eg the the Pension and
the word Scheme.

Any help is greatly appreciated.

Sam.D

Count occurance of multiple text criteria
 

Thanks, works great, huge help.

Cheers

Sam.D

"Jacob Skaria" wrote:

Try

=SUM(COUNTIF(B2:B955,{"*Pension*","*Scheme*"}))

OR for whole cell match
=SUM(COUNTIF(B2:B955,{"Pension","Scheme"}))


--
Jacob


"Sam.D" wrote:

Im using the formula

=SUM(LEN(B2:B955)-LEN(SUBSTITUTE(B2:B955,"Pension","")))/LEN("Pension")

This works fine to count the number of occurances for the word Pension but
Im stuck when trying to count using multiple criteria eg the the Pension and
the word Scheme.

Any help is greatly appreciated.

Sam.D