| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count text occurance in range | Excel Worksheet Functions | |||
| Count first occurance of text | Excel Worksheet Functions | |||
| count using multiple criteria including text strings | Excel Worksheet Functions | |||
| Count occurance of a leter in a text string | Excel Worksheet Functions | |||
| Count Occurance of Text/Word in a Range | Excel Discussion (Misc queries) | |||
Linear Mode
