Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct - find text in string
Hi all
I tried to write a sumproduct formula to find various text within a range and then sum the totals for those rows found, but my attempts failed. If anyone could suggest a formula - it would be very much appreciated: Heres what I need the formula to do: The user enters strings of words into any cells within range(B37:H165) I need the formula to look in range(B37:H165) and find any rows containing strings of text that include the words "production" or "installation" or "non commission". For those rows found, I need the formula to add all values in the same rows €“ but over in column CB (the 1st months total column) i.e. Within range(B37:H165) the formula finds that the word €śproduction€ť appears within text entered into cell C40 €śinstallation€ť appears within text entered into cell D51 €śproduction€ť appears within text entered into cell B70 €śnon commission€ť appears within text entered into cell H150 The formula then goes to range(CB37:CB165) and sums the values in in the rows found ie. =CB40+CB51+CB70+CB150 FYI - Columns €śI€ť to €śCA€ť contain general data. FYI - Columns €śCB€ť to €śCG€ť contain monthly totals. -- Thank in advance for your help BeSmart |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct - find text in string
Just to be sure we understand. If C40 contains "production" and D40 contains
"production" then should the formula be: =CB40+CB40 or =CB40 -- Gary''s Student - gsnu201001 "BeSmart" wrote: Hi all I tried to write a sumproduct formula to find various text within a range and then sum the totals for those rows found, but my attempts failed. If anyone could suggest a formula - it would be very much appreciated: Heres what I need the formula to do: The user enters strings of words into any cells within range(B37:H165) I need the formula to look in range(B37:H165) and find any rows containing strings of text that include the words "production" or "installation" or "non commission". For those rows found, I need the formula to add all values in the same rows €“ but over in column CB (the 1st months total column) i.e. Within range(B37:H165) the formula finds that the word €śproduction€ť appears within text entered into cell C40 €śinstallation€ť appears within text entered into cell D51 €śproduction€ť appears within text entered into cell B70 €śnon commission€ť appears within text entered into cell H150 The formula then goes to range(CB37:CB165) and sums the values in in the rows found ie. =CB40+CB51+CB70+CB150 FYI - Columns €śI€ť to €śCA€ť contain general data. FYI - Columns €śCB€ť to €śCG€ť contain monthly totals. -- Thank in advance for your help BeSmart |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct - find text in string
Difficult to test with data in B37:H165
I entered data in A1:D13 Some cells have text that include one of: cat, dog, horse Cells G1:G13 have numbers The formula =SUMPRODUCT( (ISNUMBER(FIND("cat",A1:D13))+ISNUMBER(FIND("dog", A1:D13))+ISNUMBER(FIND("horse",A1:D13)))*G1:G13) sum those G values that are in rows where a cell contains one of the words Note, however, if (for example) A4 has CAT and D4 has DOG then G4 gets added twice To avoid this, use helper column - I used K1:K13 In K1: =--(SUMPRODUCT(ISNUMBER(FIND("cat",A1:D1))+ISNUMBER(F IND("dog",A1:D1))+ISNUMBER(FIND("horse",A1:D1)))0 ) This is copied down the column To find required sum: =SUMPRODUCT(G1:G13,K1:K13) If required helper column (K) could be hidden best wishes -- Bernard Liengme Microsoft Excel MVP people.stfx.ca/bliengme email address: remove uppercase characters "BeSmart" wrote in message ... Hi all I tried to write a sumproduct formula to find various text within a range and then sum the totals for those rows found, but my attempts failed. If anyone could suggest a formula - it would be very much appreciated: Heres what I need the formula to do: The user enters strings of words into any cells within range(B37:H165) I need the formula to look in range(B37:H165) and find any rows containing strings of text that include the words "production" or "installation" or "non commission". For those rows found, I need the formula to add all values in the same rows €“ but over in column CB (the 1st months total column) i.e. Within range(B37:H165) the formula finds that the word €śproduction€ť appears within text entered into cell C40 €śinstallation€ť appears within text entered into cell D51 €śproduction€ť appears within text entered into cell B70 €śnon commission€ť appears within text entered into cell H150 The formula then goes to range(CB37:CB165) and sums the values in in the rows found ie. =CB40+CB51+CB70+CB150 FYI - Columns €śI€ť to €śCA€ť contain general data. FYI - Columns €śCB€ť to €śCG€ť contain monthly totals. -- Thank in advance for your help BeSmart |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct - find text in string
Let's examine this simplified data set:
....Ax.....oB.....C.....3 ....Xu.....T.....pA.....5 ....B.....O.....Cy.....10 ....Ti.....iA.....Ai.....8 You want to look for cells that contain A or B or C anywhere within the cell and sum the corresponding value. Based on that sample data what result do you expect? Would the correct result be: (3*3)+(1*5)+(2*10)+(2*8) = 50 Or: 3+5+10+8 = 26 -- Biff Microsoft Excel MVP "BeSmart" wrote in message ... Hi all I tried to write a sumproduct formula to find various text within a range and then sum the totals for those rows found, but my attempts failed. If anyone could suggest a formula - it would be very much appreciated: Here's what I need the formula to do: The user enters strings of words into any cells within range(B37:H165) I need the formula to look in range(B37:H165) and find any rows containing strings of text that include the words "production" or "installation" or "non commission". For those rows found, I need the formula to add all values in the same rows - but over in column CB (the 1st months total column) i.e. Within range(B37:H165) the formula finds that the word "production" appears within text entered into cell C40 "installation" appears within text entered into cell D51 "production" appears within text entered into cell B70 "non commission" appears within text entered into cell H150 The formula then goes to range(CB37:CB165) and sums the values in in the rows found ie. =CB40+CB51+CB70+CB150 FYI - Columns "I" to "CA" contain general data. FYI - Columns "CB" to "CG" contain monthly totals. -- Thank in advance for your help BeSmart |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct - find text in string
Hi
Thanks heaps for your assistance & questions. I need the row to be counted once if the word is found twice in the same row. Although this is unlikely to happen as the other columns require information not relating to my three words. However you never know with a user... So to use Biff's example the calculation for his table would be: .....Ax.....oB.....C.....3 .....Xu.....T.....pA.....5 .....B.....O.....Cy.....10 .....Ti.....iA.....Ai.....8 3+5+10+8=26 Perhaps the safest and simpliest way to do this (as Bernard suggested) would be to have a hidden helper column? Or is there a way to avoid duplication within the formula? -- Thank for your help BeSmart "T. Valko" wrote: Let's examine this simplified data set: ....Ax.....oB.....C.....3 ....Xu.....T.....pA.....5 ....B.....O.....Cy.....10 ....Ti.....iA.....Ai.....8 You want to look for cells that contain A or B or C anywhere within the cell and sum the corresponding value. Based on that sample data what result do you expect? Would the correct result be: (3*3)+(1*5)+(2*10)+(2*8) = 50 Or: 3+5+10+8 = 26 -- Biff Microsoft Excel MVP "BeSmart" wrote in message ... Hi all I tried to write a sumproduct formula to find various text within a range and then sum the totals for those rows found, but my attempts failed. If anyone could suggest a formula - it would be very much appreciated: Here's what I need the formula to do: The user enters strings of words into any cells within range(B37:H165) I need the formula to look in range(B37:H165) and find any rows containing strings of text that include the words "production" or "installation" or "non commission". For those rows found, I need the formula to add all values in the same rows - but over in column CB (the 1st months total column) i.e. Within range(B37:H165) the formula finds that the word "production" appears within text entered into cell C40 "installation" appears within text entered into cell D51 "production" appears within text entered into cell B70 "non commission" appears within text entered into cell H150 The formula then goes to range(CB37:CB165) and sums the values in in the rows found ie. =CB40+CB51+CB70+CB150 FYI - Columns "I" to "CA" contain general data. FYI - Columns "CB" to "CG" contain monthly totals. -- Thank in advance for your help BeSmart . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct - find text in string
Try this...
Use cells to hold the criteria. Note: these cells *must* be in a horizontal range. A32 = production B32 = installation C32 = non commission Then: =SUMPRODUCT(--(MMULT(--(COUNTIF(OFFSET(B37:H165,ROW(B37:H165)-ROW(B37),,1),"*"&A32:C32&"*")0),{1;1;1})0),CB37: CB165) -- Biff Microsoft Excel MVP "BeSmart" wrote in message ... Hi Thanks heaps for your assistance & questions. I need the row to be counted once if the word is found twice in the same row. Although this is unlikely to happen as the other columns require information not relating to my three words. However you never know with a user... So to use Biff's example the calculation for his table would be: ....Ax.....oB.....C.....3 ....Xu.....T.....pA.....5 ....B.....O.....Cy.....10 ....Ti.....iA.....Ai.....8 3+5+10+8=26 Perhaps the safest and simpliest way to do this (as Bernard suggested) would be to have a hidden helper column? Or is there a way to avoid duplication within the formula? -- Thank for your help BeSmart "T. Valko" wrote: Let's examine this simplified data set: ....Ax.....oB.....C.....3 ....Xu.....T.....pA.....5 ....B.....O.....Cy.....10 ....Ti.....iA.....Ai.....8 You want to look for cells that contain A or B or C anywhere within the cell and sum the corresponding value. Based on that sample data what result do you expect? Would the correct result be: (3*3)+(1*5)+(2*10)+(2*8) = 50 Or: 3+5+10+8 = 26 -- Biff Microsoft Excel MVP "BeSmart" wrote in message ... Hi all I tried to write a sumproduct formula to find various text within a range and then sum the totals for those rows found, but my attempts failed. If anyone could suggest a formula - it would be very much appreciated: Here's what I need the formula to do: The user enters strings of words into any cells within range(B37:H165) I need the formula to look in range(B37:H165) and find any rows containing strings of text that include the words "production" or "installation" or "non commission". For those rows found, I need the formula to add all values in the same rows - but over in column CB (the 1st months total column) i.e. Within range(B37:H165) the formula finds that the word "production" appears within text entered into cell C40 "installation" appears within text entered into cell D51 "production" appears within text entered into cell B70 "non commission" appears within text entered into cell H150 The formula then goes to range(CB37:CB165) and sums the values in in the rows found ie. =CB40+CB51+CB70+CB150 FYI - Columns "I" to "CA" contain general data. FYI - Columns "CB" to "CG" contain monthly totals. -- Thank in advance for your help BeSmart . |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct - find text in string
Minor tweak that saves a few keystrokes:
=SUMPRODUCT(--(MMULT(COUNTIF(OFFSET(B37:H165,ROW(B37:H165)-ROW(B37),,1),"*"&A32:C32&"*"),{1;1;1})0),CB37:CB1 65) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... Use cells to hold the criteria. Note: these cells *must* be in a horizontal range. A32 = production B32 = installation C32 = non commission Then: =SUMPRODUCT(--(MMULT(--(COUNTIF(OFFSET(B37:H165,ROW(B37:H165)-ROW(B37),,1),"*"&A32:C32&"*")0),{1;1;1})0),CB37: CB165) -- Biff Microsoft Excel MVP "BeSmart" wrote in message ... Hi Thanks heaps for your assistance & questions. I need the row to be counted once if the word is found twice in the same row. Although this is unlikely to happen as the other columns require information not relating to my three words. However you never know with a user... So to use Biff's example the calculation for his table would be: ....Ax.....oB.....C.....3 ....Xu.....T.....pA.....5 ....B.....O.....Cy.....10 ....Ti.....iA.....Ai.....8 3+5+10+8=26 Perhaps the safest and simpliest way to do this (as Bernard suggested) would be to have a hidden helper column? Or is there a way to avoid duplication within the formula? -- Thank for your help BeSmart "T. Valko" wrote: Let's examine this simplified data set: ....Ax.....oB.....C.....3 ....Xu.....T.....pA.....5 ....B.....O.....Cy.....10 ....Ti.....iA.....Ai.....8 You want to look for cells that contain A or B or C anywhere within the cell and sum the corresponding value. Based on that sample data what result do you expect? Would the correct result be: (3*3)+(1*5)+(2*10)+(2*8) = 50 Or: 3+5+10+8 = 26 -- Biff Microsoft Excel MVP "BeSmart" wrote in message ... Hi all I tried to write a sumproduct formula to find various text within a range and then sum the totals for those rows found, but my attempts failed. If anyone could suggest a formula - it would be very much appreciated: Here's what I need the formula to do: The user enters strings of words into any cells within range(B37:H165) I need the formula to look in range(B37:H165) and find any rows containing strings of text that include the words "production" or "installation" or "non commission". For those rows found, I need the formula to add all values in the same rows - but over in column CB (the 1st months total column) i.e. Within range(B37:H165) the formula finds that the word "production" appears within text entered into cell C40 "installation" appears within text entered into cell D51 "production" appears within text entered into cell B70 "non commission" appears within text entered into cell H150 The formula then goes to range(CB37:CB165) and sums the values in in the rows found ie. =CB40+CB51+CB70+CB150 FYI - Columns "I" to "CA" contain general data. FYI - Columns "CB" to "CG" contain monthly totals. -- Thank in advance for your help BeSmart . |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct - find text in string
Thanks Biff
It looks complicated (I'm not sure that I totally understand what it's doing - but I get the general idea) What really matters is that works brilliantly and helps me immensely!!! Thank you very much for spending the time to help me!! Cheers BeSmart |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct - find text in string
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "BeSmart" wrote in message ... Thanks Biff It looks complicated (I'm not sure that I totally understand what it's doing - but I get the general idea) What really matters is that works brilliantly and helps me immensely!!! Thank you very much for spending the time to help me!! Cheers BeSmart |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Find Specific Text in a Text String | Excel Discussion (Misc queries) | |||
Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
find a variable text string | Excel Discussion (Misc queries) | |||
find text in a string formula | Excel Worksheet Functions | |||
can you find specific text in a string ignoring any other text | Excel Discussion (Misc queries) |