variable text value within string formula required to sum column

  • Thread starter Thread starter Tester
  • Start date Start date
T

Tester

My column D4:D1002 contains text and they could all conceivably be
different. Some of them have words in them that will be the same as in
others, for example the word "insurance" might be many different cells
within text strings which are unique to that cell, like Norwich Union
Insurance or Health Insurance Plan or Insurance or Insurance Fees.

I need to pick out those cells and sum the corresponding cell in column F or
G, whichever has the figure in it.

Any suggestions please?
TIA

Chris
 
My column D4:D1002 contains text and they could all conceivably be
different. Some of them have words in them that will be the same as in
others, for example the word "insurance" might be many different cells
within text strings which are unique to that cell, like Norwich Union
Insurance or Health Insurance Plan or Insurance or Insurance Fees.

I need to pick out those cells and sum the corresponding cell in column F or
G, whichever has the figure in it.

Any suggestions please?
TIA

Chris
Click to expand...


=SUMIF(D4:D1002,"*insurance*",F4:F1002) +
SUMIF(D4:D1002,"*insurance*",G4:G1002)

Or, if the word to search for is in a cell, let us say A1, then:

=SUMIF(D4:D1002,"*"&A1&"*",F4:F1002) + SUMIF(D4:D1002,"*"&A1&"*",G4:G1002)

Note that the above will detect the string even if it is part of a word, as
opposed to being the entire word. So if A1 contained "insure", the formula
would also detect words like

reinsure
uninsured
insured

etc.

If this is a problem a different solution might be more appropriate.
--ron
 
Back
Top