variable text value within string formula required to sum column
 

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

variable text value within string formula required to sum column
 

On Thu, 7 Dec 2006 23:02:46 -0000, "Tester" wrote:

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



=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

variable text value within string formula required to sum column
 

Another way would be to use the Autofilter and choose "Custom" contains
insurance..........this would let you see the rows of concern..........then
use the SUBTOTAL formula to sum the desired column

Vaya con Dios,
Chuck, CABGx3



"Tester" wrote:

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