Finding text in a cell

**Tom_Fernley** · 02-20-2008, 08:00 AM

Hello everyone,

Really sorry, but the search function isn't working for me at the moment, so if these answers are elsewhere, apologies...

In the attached file, I need to count the total number of tours and inspections, planned vs. actual, across the year. What I'm stuck on is finding occurrences of a given set of initials when they're part of a larger text string.

I can make sure that the initials are always delimited with hyphens (or any other character), with no spaces. I'd like to avoid large tracts of hidden rows and columns, if possible.

Thanks,

Tom

**NBVC** · 02-20-2008, 08:59 AM

Not sure exactly what you are looking for....

Perhaps you should specify which cells are to show your results and where to look for the results based on what condition or conditions.

Btw, the formula in H58 of Safety Tours & PGIs can be simplified to:

=SUMPRODUCT(($D$43:$AB$50=$C58)*($D$42:$AB$42=H$57))+SUMPRODUCT(($D$25:$AB$28=$C58)*($D$24:$AB$24=H$57))+SUMPRODUCT(($D$9:$AB$12=$C58)*($D$8:$AB$8=H$57))

copied down and across

**Tom_Fernley** · 02-20-2008, 10:30 AM

Thanks for the suggestion of sumproduct NVC - that may help!

EDIT: This all refers to the worksheet named 'DRAFT' - sorry for the confusion.

On the initial subject, I want cell G13 to tell me the number of times the initials SML show up in the range C4:D7, where the cells may contain strings such as SML-MS, VS-SML or just SML. Similarly, cell G14 needs to show the same data for initials MS, under the same conditions, and so on.

I've simplified the spreadsheet to be indicative - it actually stretches across 13 weeks and about a dozen people, with every possible combination of those initials.

Thanks again,

Tom

**NBVC** · 02-20-2008, 10:41 AM

Try in G13:

=SUMPRODUCT(ISNUMBER(SEARCH($B13,$C$4:$D$7))*($C$3:$D$3="Planned"))

and in H13:

=SUMPRODUCT(ISNUMBER(SEARCH($B13,$C$4:$D$7))*($C$3:$D$3="Actual"))

then copy both formulas down.