Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
Search column and move text formula | Excel Discussion (Misc queries) | |||
Putting text in a column based on variable text from another colum | Excel Discussion (Misc queries) |