|
SUMIF or SUMPRODUCT to total cells containing multiple texts
I am attempting to set up a spreadsheet whereby I can summarise the weight
load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
Hi
How many two letter combinations are there? Does the cell in column B always contain 3 sets of letters, or, can there be 1, 2, 3 (or more) sets? I would be inclined (subject to the answers to the above) to create a further series of entries in IU and IV ah zy 5.9 zy ah 5.9 ah pd 3.6 pd ah 3.6 ah zy pd 7.3 plus all the other combinations Then use =VLOOKUP(B1,IU:IV,2,0) -- Regards Roger Govier "Terranoman" wrote in message ... I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
There can be multiple combinations, there could be a minimum of 3 up to a
maximum of 15 in the cell, but the combinations will not always be the same (this is dependant on the items being shipped). Thanks "Roger Govier" wrote: Hi How many two letter combinations are there? Does the cell in column B always contain 3 sets of letters, or, can there be 1, 2, 3 (or more) sets? I would be inclined (subject to the answers to the above) to create a further series of entries in IU and IV ah zy 5.9 zy ah 5.9 ah pd 3.6 pd ah 3.6 ah zy pd 7.3 plus all the other combinations Then use =VLOOKUP(B1,IU:IV,2,0) -- Regards Roger Govier "Terranoman" wrote in message ... I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
Hi
If that's the case, then other than some VBA code to carry out the task, I would do all the calculation on a separate sheet. Copy Columns IU and IV to columns A and B of sheet2. Assuming your entry with the strings of 2 characters is in column A of Sheet1, then in cell C1 of Sheet2 enter =IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*3,2)) copy across through D1:R1 In cell S1 of Sheet2 enter =IF(C1="","",VLOOKUP(C1,$A:$B,2,0)) Copy across through cells T1:AJ1 in cell AK1 =SUM(S1:AJ1) Copy C1:AK1 down as far as required On sheet1 in cell B1 to calculate the total weight, use =Sheet2!AK1 -- Regards Roger Govier "Terranoman" wrote in message ... There can be multiple combinations, there could be a minimum of 3 up to a maximum of 15 in the cell, but the combinations will not always be the same (this is dependant on the items being shipped). Thanks "Roger Govier" wrote: Hi How many two letter combinations are there? Does the cell in column B always contain 3 sets of letters, or, can there be 1, 2, 3 (or more) sets? I would be inclined (subject to the answers to the above) to create a further series of entries in IU and IV ah zy 5.9 zy ah 5.9 ah pd 3.6 pd ah 3.6 ah zy pd 7.3 plus all the other combinations Then use =VLOOKUP(B1,IU:IV,2,0) -- Regards Roger Govier "Terranoman" wrote in message ... I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
I managed it with a formula that basically looks like this assuming the data
is in B2 =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE( IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)), MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0))) Unfortunately, that has too many nested functions, so I had to split out MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and define it as a name (Ctrl-F3). I used a name of matches and a Refersto value of =MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and then my formula is =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. I also had to enter a dummy value in row 1 of your lookup table with a weight of 0 to get it to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Terranoman" wrote in message ... I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
Hi Bob
Phhhewwww..... That's great. Just a very small point, the OP was using IU and IV for his columns for the lookup table, not U and I and he did not say he was using Sheet2. I used a second sheet in my suggestion. As long as he makes those minor mods, he will have a superb solution. I had totally missed that there were comma's separating the values in the source cell in my rather mundane solution which would necessitate a change to the formula to =IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2)) I think he should go with your solution. -- Regards Roger Govier "Bob Phillips" wrote in message ... I managed it with a formula that basically looks like this assuming the data is in B2 =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE( IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)), MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0))) Unfortunately, that has too many nested functions, so I had to split out MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and define it as a name (Ctrl-F3). I used a name of matches and a Refersto value of =MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and then my formula is =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. I also had to enter a dummy value in row 1 of your lookup table with a weight of 0 to get it to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Terranoman" wrote in message ... I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
Sorry about the IU IV, I built it using U and V as I could see those
columns. Personally, I think he should re-design his spreadsheet <bg -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi Bob Phhhewwww..... That's great. Just a very small point, the OP was using IU and IV for his columns for the lookup table, not U and I and he did not say he was using Sheet2. I used a second sheet in my suggestion. As long as he makes those minor mods, he will have a superb solution. I had totally missed that there were comma's separating the values in the source cell in my rather mundane solution which would necessitate a change to the formula to =IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2)) I think he should go with your solution. -- Regards Roger Govier "Bob Phillips" wrote in message ... I managed it with a formula that basically looks like this assuming the data is in B2 =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE( IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)), MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0))) Unfortunately, that has too many nested functions, so I had to split out MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and define it as a name (Ctrl-F3). I used a name of matches and a Refersto value of =MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and then my formula is =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. I also had to enter a dummy value in row 1 of your lookup table with a weight of 0 to get it to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Terranoman" wrote in message ... I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
not U and I
should have read not U and V -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Bob Phhhewwww..... That's great. Just a very small point, the OP was using IU and IV for his columns for the lookup table, not U and I and he did not say he was using Sheet2. I used a second sheet in my suggestion. As long as he makes those minor mods, he will have a superb solution. I had totally missed that there were comma's separating the values in the source cell in my rather mundane solution which would necessitate a change to the formula to =IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2)) I think he should go with your solution. -- Regards Roger Govier "Bob Phillips" wrote in message ... I managed it with a formula that basically looks like this assuming the data is in B2 =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE( IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)), MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0))) Unfortunately, that has too many nested functions, so I had to split out MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and define it as a name (Ctrl-F3). I used a name of matches and a Refersto value of =MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and then my formula is =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. I also had to enter a dummy value in row 1 of your lookup table with a weight of 0 to get it to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Terranoman" wrote in message ... I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
Personally, I think he should re-design his spreadsheet <bg
Well, funnily enough, that thought also crossed my mind<g but seeing as he was using the last 2 columns, I wondered whether it was so horrendous that all columns were being used, and the values couldn't be split out into other columns. That's why I opted for using 2nd sheet. -- Regards Roger Govier "Bob Phillips" wrote in message ... Sorry about the IU IV, I built it using U and V as I could see those columns. Personally, I think he should re-design his spreadsheet <bg -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi Bob Phhhewwww..... That's great. Just a very small point, the OP was using IU and IV for his columns for the lookup table, not U and I and he did not say he was using Sheet2. I used a second sheet in my suggestion. As long as he makes those minor mods, he will have a superb solution. I had totally missed that there were comma's separating the values in the source cell in my rather mundane solution which would necessitate a change to the formula to =IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2)) I think he should go with your solution. -- Regards Roger Govier "Bob Phillips" wrote in message ... I managed it with a formula that basically looks like this assuming the data is in B2 =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE( IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)), MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0))) Unfortunately, that has too many nested functions, so I had to split out MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and define it as a name (Ctrl-F3). I used a name of matches and a Refersto value of =MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and then my formula is =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. I also had to enter a dummy value in row 1 of your lookup table with a weight of 0 to get it to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Terranoman" wrote in message ... I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
In this case Roger, I think it needs a BIG re-design. I tried to concoct a
formula where the values were not comma separated in one cell, but many cells, and it wasn't much better. This took me a while to figure out, I wouldn't want to come to this spreadsheet and see that monster unprepared, so I think the maintenance is probably not worth it. Start again with the requirement, and come up with a simpler design. Often works for me. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Personally, I think he should re-design his spreadsheet <bg Well, funnily enough, that thought also crossed my mind<g but seeing as he was using the last 2 columns, I wondered whether it was so horrendous that all columns were being used, and the values couldn't be split out into other columns. That's why I opted for using 2nd sheet. -- Regards Roger Govier "Bob Phillips" wrote in message ... Sorry about the IU IV, I built it using U and V as I could see those columns. Personally, I think he should re-design his spreadsheet <bg -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi Bob Phhhewwww..... That's great. Just a very small point, the OP was using IU and IV for his columns for the lookup table, not U and I and he did not say he was using Sheet2. I used a second sheet in my suggestion. As long as he makes those minor mods, he will have a superb solution. I had totally missed that there were comma's separating the values in the source cell in my rather mundane solution which would necessitate a change to the formula to =IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2)) I think he should go with your solution. -- Regards Roger Govier "Bob Phillips" wrote in message ... I managed it with a formula that basically looks like this assuming the data is in B2 =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE( IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)), MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0))) Unfortunately, that has too many nested functions, so I had to split out MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and define it as a name (Ctrl-F3). I used a name of matches and a Refersto value of =MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and then my formula is =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. I also had to enter a dummy value in row 1 of your lookup table with a weight of 0 to get it to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Terranoman" wrote in message ... I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
Unfortunately I've inherited the spreadsheet and attempting to make it work
(without the use of a calculator and pen). Is it possible that I could forward it on for perusal? You guys are way beyond my excel knowledge. "Roger Govier" wrote: not U and I should have read not U and V -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Bob Phhhewwww..... That's great. Just a very small point, the OP was using IU and IV for his columns for the lookup table, not U and I and he did not say he was using Sheet2. I used a second sheet in my suggestion. As long as he makes those minor mods, he will have a superb solution. I had totally missed that there were comma's separating the values in the source cell in my rather mundane solution which would necessitate a change to the formula to =IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2)) I think he should go with your solution. -- Regards Roger Govier "Bob Phillips" wrote in message ... I managed it with a formula that basically looks like this assuming the data is in B2 =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE( IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)), MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0))) Unfortunately, that has too many nested functions, so I had to split out MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and define it as a name (Ctrl-F3). I used a name of matches and a Refersto value of =MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and then my formula is =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. I also had to enter a dummy value in row 1 of your lookup table with a weight of 0 to get it to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Terranoman" wrote in message ... I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
Doesn't my solution work then?
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Terranoman" wrote in message ... Unfortunately I've inherited the spreadsheet and attempting to make it work (without the use of a calculator and pen). Is it possible that I could forward it on for perusal? You guys are way beyond my excel knowledge. "Roger Govier" wrote: not U and I should have read not U and V -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Bob Phhhewwww..... That's great. Just a very small point, the OP was using IU and IV for his columns for the lookup table, not U and I and he did not say he was using Sheet2. I used a second sheet in my suggestion. As long as he makes those minor mods, he will have a superb solution. I had totally missed that there were comma's separating the values in the source cell in my rather mundane solution which would necessitate a change to the formula to =IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2)) I think he should go with your solution. -- Regards Roger Govier "Bob Phillips" wrote in message ... I managed it with a formula that basically looks like this assuming the data is in B2 =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE( IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)), MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0))) Unfortunately, that has too many nested functions, so I had to split out MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and define it as a name (Ctrl-F3). I used a name of matches and a Refersto value of =MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and then my formula is =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. I also had to enter a dummy value in row 1 of your lookup table with a weight of 0 to get it to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Terranoman" wrote in message ... I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
Hi
You can send it to me to look at if you wish. Remove NOSPAM from my email address to mail direct. I am going out now, but will take a look later this evening. -- Regards Roger Govier "Terranoman" wrote in message ... Unfortunately I've inherited the spreadsheet and attempting to make it work (without the use of a calculator and pen). Is it possible that I could forward it on for perusal? You guys are way beyond my excel knowledge. "Roger Govier" wrote: not U and I should have read not U and V -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Bob Phhhewwww..... That's great. Just a very small point, the OP was using IU and IV for his columns for the lookup table, not U and I and he did not say he was using Sheet2. I used a second sheet in my suggestion. As long as he makes those minor mods, he will have a superb solution. I had totally missed that there were comma's separating the values in the source cell in my rather mundane solution which would necessitate a change to the formula to =IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2)) I think he should go with your solution. -- Regards Roger Govier "Bob Phillips" wrote in message ... I managed it with a formula that basically looks like this assuming the data is in B2 =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE( IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)), MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0))) Unfortunately, that has too many nested functions, so I had to split out MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and define it as a name (Ctrl-F3). I used a name of matches and a Refersto value of =MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and then my formula is =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. I also had to enter a dummy value in row 1 of your lookup table with a weight of 0 to get it to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Terranoman" wrote in message ... I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
Again, I feel like thinking aloud even though I don't have a clue of what's going on.
Personally, I think he should re-design his spreadsheet <bg That was the first thought that came to my mind when I briefly read the post. <g Unfortunately I've inherited the spreadsheet and attempting to make it work (without the use of a calculator and pen)....... You guys are way beyond my excel knowledge. I feel so sorry that Terranoman has to put up with the inheritance. Bob and Roger, I just want to point out this is exactly why I always focus on and have so many questions on speed etc. I want to learn to do it right (i.e. design the spreadsheet properly) in my early stage of learning so that I can save myself and others from pain and suffering when it comes to composing formulae down the road. I hope you understand my reasons behind my numerous questions. Your support and guidance are always appreciated. Epinn "Roger Govier" wrote in message ... Personally, I think he should re-design his spreadsheet <bg Well, funnily enough, that thought also crossed my mind<g but seeing as he was using the last 2 columns, I wondered whether it was so horrendous that all columns were being used, and the values couldn't be split out into other columns. That's why I opted for using 2nd sheet. -- Regards Roger Govier "Bob Phillips" wrote in message ... Sorry about the IU IV, I built it using U and V as I could see those columns. Personally, I think he should re-design his spreadsheet <bg -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi Bob Phhhewwww..... That's great. Just a very small point, the OP was using IU and IV for his columns for the lookup table, not U and I and he did not say he was using Sheet2. I used a second sheet in my suggestion. As long as he makes those minor mods, he will have a superb solution. I had totally missed that there were comma's separating the values in the source cell in my rather mundane solution which would necessitate a change to the formula to =IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2)) I think he should go with your solution. -- Regards Roger Govier "Bob Phillips" wrote in message ... I managed it with a formula that basically looks like this assuming the data is in B2 =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE( IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)), MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0))) Unfortunately, that has too many nested functions, so I had to split out MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and define it as a name (Ctrl-F3). I used a name of matches and a Refersto value of =MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and then my formula is =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. I also had to enter a dummy value in row 1 of your lookup table with a weight of 0 to get it to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Terranoman" wrote in message ... I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
Epinn,
Nobody minds your questions, you would soon hear if we did <bg. I can't speak for the OPs though whose threads you hijack <ebg -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... Again, I feel like thinking aloud even though I don't have a clue of what's going on. Personally, I think he should re-design his spreadsheet <bg That was the first thought that came to my mind when I briefly read the post. <g Unfortunately I've inherited the spreadsheet and attempting to make it work (without the use of a calculator and pen)....... You guys are way beyond my excel knowledge. I feel so sorry that Terranoman has to put up with the inheritance. Bob and Roger, I just want to point out this is exactly why I always focus on and have so many questions on speed etc. I want to learn to do it right (i.e. design the spreadsheet properly) in my early stage of learning so that I can save myself and others from pain and suffering when it comes to composing formulae down the road. I hope you understand my reasons behind my numerous questions. Your support and guidance are always appreciated. Epinn "Roger Govier" wrote in message ... Personally, I think he should re-design his spreadsheet <bg Well, funnily enough, that thought also crossed my mind<g but seeing as he was using the last 2 columns, I wondered whether it was so horrendous that all columns were being used, and the values couldn't be split out into other columns. That's why I opted for using 2nd sheet. -- Regards Roger Govier "Bob Phillips" wrote in message ... Sorry about the IU IV, I built it using U and V as I could see those columns. Personally, I think he should re-design his spreadsheet <bg -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi Bob Phhhewwww..... That's great. Just a very small point, the OP was using IU and IV for his columns for the lookup table, not U and I and he did not say he was using Sheet2. I used a second sheet in my suggestion. As long as he makes those minor mods, he will have a superb solution. I had totally missed that there were comma's separating the values in the source cell in my rather mundane solution which would necessitate a change to the formula to =IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2)) I think he should go with your solution. -- Regards Roger Govier "Bob Phillips" wrote in message ... I managed it with a formula that basically looks like this assuming the data is in B2 =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE( IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)), MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0))) Unfortunately, that has too many nested functions, so I had to split out MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and define it as a name (Ctrl-F3). I used a name of matches and a Refersto value of =MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and then my formula is =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. I also had to enter a dummy value in row 1 of your lookup table with a weight of 0 to get it to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Terranoman" wrote in message ... I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
Roger,
Do post the final solution back here, we have invested a lot in this one. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi You can send it to me to look at if you wish. Remove NOSPAM from my email address to mail direct. I am going out now, but will take a look later this evening. -- Regards Roger Govier "Terranoman" wrote in message ... Unfortunately I've inherited the spreadsheet and attempting to make it work (without the use of a calculator and pen). Is it possible that I could forward it on for perusal? You guys are way beyond my excel knowledge. "Roger Govier" wrote: not U and I should have read not U and V -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Bob Phhhewwww..... That's great. Just a very small point, the OP was using IU and IV for his columns for the lookup table, not U and I and he did not say he was using Sheet2. I used a second sheet in my suggestion. As long as he makes those minor mods, he will have a superb solution. I had totally missed that there were comma's separating the values in the source cell in my rather mundane solution which would necessitate a change to the formula to =IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2)) I think he should go with your solution. -- Regards Roger Govier "Bob Phillips" wrote in message ... I managed it with a formula that basically looks like this assuming the data is in B2 =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE( IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)), MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0))) Unfortunately, that has too many nested functions, so I had to split out MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and define it as a name (Ctrl-F3). I used a name of matches and a Refersto value of =MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and then my formula is =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. I also had to enter a dummy value in row 1 of your lookup table with a weight of 0 to get it to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Terranoman" wrote in message ... I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
Sure will Bob, if ever I receive the file.
I have been out all afternoon, but on checking mail this evening, nothing has arrived yet. I will give the OP another nudge. -- Regards Roger Govier "Bob Phillips" wrote in message ... Roger, Do post the final solution back here, we have invested a lot in this one. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi You can send it to me to look at if you wish. Remove NOSPAM from my email address to mail direct. I am going out now, but will take a look later this evening. -- Regards Roger Govier "Terranoman" wrote in message ... Unfortunately I've inherited the spreadsheet and attempting to make it work (without the use of a calculator and pen). Is it possible that I could forward it on for perusal? You guys are way beyond my excel knowledge. "Roger Govier" wrote: not U and I should have read not U and V -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Bob Phhhewwww..... That's great. Just a very small point, the OP was using IU and IV for his columns for the lookup table, not U and I and he did not say he was using Sheet2. I used a second sheet in my suggestion. As long as he makes those minor mods, he will have a superb solution. I had totally missed that there were comma's separating the values in the source cell in my rather mundane solution which would necessitate a change to the formula to =IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2)) I think he should go with your solution. -- Regards Roger Govier "Bob Phillips" wrote in message ... I managed it with a formula that basically looks like this assuming the data is in B2 =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE( IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)), MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0))) Unfortunately, that has too many nested functions, so I had to split out MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and define it as a name (Ctrl-F3). I used a name of matches and a Refersto value of =MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and then my formula is =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. I also had to enter a dummy value in row 1 of your lookup table with a weight of 0 to get it to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Terranoman" wrote in message ... I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
Hi
As I said earlier, you can mail the file to me if you wish. remove NOSPAM from my address to mail direct i.e roger at technology4u dot co dot uk Do the obvious with the spaces and dots. I will be pleased to take a look and post back. -- Regards Roger Govier "Terranoman" wrote in message ... Unfortunately I've inherited the spreadsheet and attempting to make it work (without the use of a calculator and pen). Is it possible that I could forward it on for perusal? You guys are way beyond my excel knowledge. "Roger Govier" wrote: not U and I should have read not U and V -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Bob Phhhewwww..... That's great. Just a very small point, the OP was using IU and IV for his columns for the lookup table, not U and I and he did not say he was using Sheet2. I used a second sheet in my suggestion. As long as he makes those minor mods, he will have a superb solution. I had totally missed that there were comma's separating the values in the source cell in my rather mundane solution which would necessitate a change to the formula to =IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2)) I think he should go with your solution. -- Regards Roger Govier "Bob Phillips" wrote in message ... I managed it with a formula that basically looks like this assuming the data is in B2 =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE( IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)), MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0))) Unfortunately, that has too many nested functions, so I had to split out MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and define it as a name (Ctrl-F3). I used a name of matches and a Refersto value of =MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and then my formula is =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. I also had to enter a dummy value in row 1 of your lookup table with a weight of 0 to get it to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Terranoman" wrote in message ... I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
<< Nobody minds your questions......
Not everyone is as kind and patient as most of you are. I have to be cautious ...... << ...... hijack ...... I know, Bob. This is a sensitive issue and it has always been a concern for me. I try to apologize before anyone complains. I always seem to have more questions than anyone around. If I think open group discussion, archive, easy reference for future research etc. then I can justify asking my questions or providing my comments under the original poster's thread. Having said this, I do understand that the poster has "ownership" and certain privilege and it is also natural that he/she feels "possessive" and doesn't want others to ask many questions or "take over" ...... I usually hold off my questions until the poster has got the answers and "left" the forum. What is the right approach? When unsure, shut up? Epinn "Bob Phillips" wrote in message ... Epinn, Nobody minds your questions, you would soon hear if we did <bg. I can't speak for the OPs though whose threads you hijack <ebg -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... Again, I feel like thinking aloud even though I don't have a clue of what's going on. Personally, I think he should re-design his spreadsheet <bg That was the first thought that came to my mind when I briefly read the post. <g Unfortunately I've inherited the spreadsheet and attempting to make it work (without the use of a calculator and pen)....... You guys are way beyond my excel knowledge. I feel so sorry that Terranoman has to put up with the inheritance. Bob and Roger, I just want to point out this is exactly why I always focus on and have so many questions on speed etc. I want to learn to do it right (i.e. design the spreadsheet properly) in my early stage of learning so that I can save myself and others from pain and suffering when it comes to composing formulae down the road. I hope you understand my reasons behind my numerous questions. Your support and guidance are always appreciated. Epinn "Roger Govier" wrote in message ... Personally, I think he should re-design his spreadsheet <bg Well, funnily enough, that thought also crossed my mind<g but seeing as he was using the last 2 columns, I wondered whether it was so horrendous that all columns were being used, and the values couldn't be split out into other columns. That's why I opted for using 2nd sheet. -- Regards Roger Govier "Bob Phillips" wrote in message ... Sorry about the IU IV, I built it using U and V as I could see those columns. Personally, I think he should re-design his spreadsheet <bg -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi Bob Phhhewwww..... That's great. Just a very small point, the OP was using IU and IV for his columns for the lookup table, not U and I and he did not say he was using Sheet2. I used a second sheet in my suggestion. As long as he makes those minor mods, he will have a superb solution. I had totally missed that there were comma's separating the values in the source cell in my rather mundane solution which would necessitate a change to the formula to =IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2)) I think he should go with your solution. -- Regards Roger Govier "Bob Phillips" wrote in message ... I managed it with a formula that basically looks like this assuming the data is in B2 =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE( IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)), MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0))) Unfortunately, that has too many nested functions, so I had to split out MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and define it as a name (Ctrl-F3). I used a name of matches and a Refersto value of =MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and then my formula is =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. I also had to enter a dummy value in row 1 of your lookup table with a weight of 0 to get it to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Terranoman" wrote in message ... I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
=SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 )
Adjust to your ranges and Drag-fill as needed. -- Festina Lente "Terranoman" wrote: I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
Absolutely not. If it were me, I think I would open a new thread as a
discussion thread, referencing back to the thread that made you stop and think. That way, the OPs thread is left to run its course (we have to be careful not to intimidate/scare off posters from asking follow-ups), but your questions get a very clear forum of their own which people can join or ignore as is there wont. But never shut up, others will also read the threads and learn from them. I go the COLUM(A2:F4)^0 trick from this, and I am sure I will use that in future. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... << Nobody minds your questions...... Not everyone is as kind and patient as most of you are. I have to be cautious ...... << ...... hijack ...... I know, Bob. This is a sensitive issue and it has always been a concern for me. I try to apologize before anyone complains. I always seem to have more questions than anyone around. If I think open group discussion, archive, easy reference for future research etc. then I can justify asking my questions or providing my comments under the original poster's thread. Having said this, I do understand that the poster has "ownership" and certain privilege and it is also natural that he/she feels "possessive" and doesn't want others to ask many questions or "take over" ...... I usually hold off my questions until the poster has got the answers and "left" the forum. What is the right approach? When unsure, shut up? Epinn "Bob Phillips" wrote in message ... Epinn, Nobody minds your questions, you would soon hear if we did <bg. I can't speak for the OPs though whose threads you hijack <ebg -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... Again, I feel like thinking aloud even though I don't have a clue of what's going on. Personally, I think he should re-design his spreadsheet <bg That was the first thought that came to my mind when I briefly read the post. <g Unfortunately I've inherited the spreadsheet and attempting to make it work (without the use of a calculator and pen)....... You guys are way beyond my excel knowledge. I feel so sorry that Terranoman has to put up with the inheritance. Bob and Roger, I just want to point out this is exactly why I always focus on and have so many questions on speed etc. I want to learn to do it right (i.e. design the spreadsheet properly) in my early stage of learning so that I can save myself and others from pain and suffering when it comes to composing formulae down the road. I hope you understand my reasons behind my numerous questions. Your support and guidance are always appreciated. Epinn "Roger Govier" wrote in message ... Personally, I think he should re-design his spreadsheet <bg Well, funnily enough, that thought also crossed my mind<g but seeing as he was using the last 2 columns, I wondered whether it was so horrendous that all columns were being used, and the values couldn't be split out into other columns. That's why I opted for using 2nd sheet. -- Regards Roger Govier "Bob Phillips" wrote in message ... Sorry about the IU IV, I built it using U and V as I could see those columns. Personally, I think he should re-design his spreadsheet <bg -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi Bob Phhhewwww..... That's great. Just a very small point, the OP was using IU and IV for his columns for the lookup table, not U and I and he did not say he was using Sheet2. I used a second sheet in my suggestion. As long as he makes those minor mods, he will have a superb solution. I had totally missed that there were comma's separating the values in the source cell in my rather mundane solution which would necessitate a change to the formula to =IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2)) I think he should go with your solution. -- Regards Roger Govier "Bob Phillips" wrote in message ... I managed it with a formula that basically looks like this assuming the data is in B2 =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE( IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)), MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0))) Unfortunately, that has too many nested functions, so I had to split out MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and define it as a name (Ctrl-F3). I used a name of matches and a Refersto value of =MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and then my formula is =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. I also had to enter a dummy value in row 1 of your lookup table with a weight of 0 to get it to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Terranoman" wrote in message ... I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
Use SEARCH instead of FIND if case sensitivity is a problem...
-- Festina Lente "PapaDos" wrote: =SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 ) Adjust to your ranges and Drag-fill as needed. -- Festina Lente "Terranoman" wrote: I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
Before I forget, I must apologize to the original poster for side tracking. Yes, Bob, opening a new thread and referencing back to the original thread may be the middle ground and I did think about this. I'll start right now. Bob, please move over to "What is the right approach?" thread.
Epinn "Bob Phillips" wrote in message ... Absolutely not. If it were me, I think I would open a new thread as a discussion thread, referencing back to the thread that made you stop and think. That way, the OPs thread is left to run its course (we have to be careful not to intimidate/scare off posters from asking follow-ups), but your questions get a very clear forum of their own which people can join or ignore as is there wont. But never shut up, others will also read the threads and learn from them. I go the COLUM(A2:F4)^0 trick from this, and I am sure I will use that in future. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... << Nobody minds your questions...... Not everyone is as kind and patient as most of you are. I have to be cautious ...... << ...... hijack ...... I know, Bob. This is a sensitive issue and it has always been a concern for me. I try to apologize before anyone complains. I always seem to have more questions than anyone around. If I think open group discussion, archive, easy reference for future research etc. then I can justify asking my questions or providing my comments under the original poster's thread. Having said this, I do understand that the poster has "ownership" and certain privilege and it is also natural that he/she feels "possessive" and doesn't want others to ask many questions or "take over" ...... I usually hold off my questions until the poster has got the answers and "left" the forum. What is the right approach? When unsure, shut up? Epinn "Bob Phillips" wrote in message ... Epinn, Nobody minds your questions, you would soon hear if we did <bg. I can't speak for the OPs though whose threads you hijack <ebg -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... Again, I feel like thinking aloud even though I don't have a clue of what's going on. Personally, I think he should re-design his spreadsheet <bg That was the first thought that came to my mind when I briefly read the post. <g Unfortunately I've inherited the spreadsheet and attempting to make it work (without the use of a calculator and pen)....... You guys are way beyond my excel knowledge. I feel so sorry that Terranoman has to put up with the inheritance. Bob and Roger, I just want to point out this is exactly why I always focus on and have so many questions on speed etc. I want to learn to do it right (i.e. design the spreadsheet properly) in my early stage of learning so that I can save myself and others from pain and suffering when it comes to composing formulae down the road. I hope you understand my reasons behind my numerous questions. Your support and guidance are always appreciated. Epinn "Roger Govier" wrote in message ... Personally, I think he should re-design his spreadsheet <bg Well, funnily enough, that thought also crossed my mind<g but seeing as he was using the last 2 columns, I wondered whether it was so horrendous that all columns were being used, and the values couldn't be split out into other columns. That's why I opted for using 2nd sheet. -- Regards Roger Govier "Bob Phillips" wrote in message ... Sorry about the IU IV, I built it using U and V as I could see those columns. Personally, I think he should re-design his spreadsheet <bg -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi Bob Phhhewwww..... That's great. Just a very small point, the OP was using IU and IV for his columns for the lookup table, not U and I and he did not say he was using Sheet2. I used a second sheet in my suggestion. As long as he makes those minor mods, he will have a superb solution. I had totally missed that there were comma's separating the values in the source cell in my rather mundane solution which would necessitate a change to the formula to =IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2)) I think he should go with your solution. -- Regards Roger Govier "Bob Phillips" wrote in message ... I managed it with a formula that basically looks like this assuming the data is in B2 =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE( IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)), MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0))) Unfortunately, that has too many nested functions, so I had to split out MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and define it as a name (Ctrl-F3). I used a name of matches and a Refersto value of =MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0) and then my formula is =SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. I also had to enter a dummy value in row 1 of your lookup table with a weight of 0 to get it to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Terranoman" wrote in message ... I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
An example of column B contents is ah, zy, pd, (in a single cell on each row)
=SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 ) I am totally lost! Can't see that the formula works. No file from Terranoman? Epinn "PapaDos" wrote in message ... =SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 ) Adjust to your ranges and Drag-fill as needed. -- Festina Lente "Terranoman" wrote: I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
LOL, it does work.
I simply approach it the other way around. Instead of looking for extracted parts of the string into a table, I look for the table column in the string... -- Festina Lente "Epinn" wrote: An example of column B contents is ah, zy, pd, (in a single cell on each row) =SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 ) I am totally lost! Can't see that the formula works. No file from Terranoman? Epinn "PapaDos" wrote in message ... =SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 ) Adjust to your ranges and Drag-fill as needed. -- Festina Lente "Terranoman" wrote: I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
Thank you for explaining. I remember the syntax for FIND incorrectly. I had the first and second argument reversed. After I have cleared that, it becomes clear to me that the formula should work. I am a fan of SUMPRODUCT, so I like what I see.
I wonder if a 50-row table is considered huge. Hope this is not too hard on the system. It will be nice if the poster provides some kind of feedback. Epinn "PapaDos" wrote in message ... Use SEARCH instead of FIND if case sensitivity is a problem... -- Festina Lente "PapaDos" wrote: =SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 ) Adjust to your ranges and Drag-fill as needed. -- Festina Lente "Terranoman" wrote: I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
Hi PapaDos
Very clever thinking. I like it!! However, if there should be a repeat of items in cell B2, then it would not get picked up for the second (or subsequent) occurrence. Bob's formula does pick up multiple occurrences. I have no idea whether the OP's data has single or multiple occurrences of items. I still have not received any file from him / her. -- Regards Roger Govier "PapaDos" wrote in message ... LOL, it does work. I simply approach it the other way around. Instead of looking for extracted parts of the string into a table, I look for the table column in the string... -- Festina Lente "Epinn" wrote: An example of column B contents is ah, zy, pd, (in a single cell on each row) =SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 ) I am totally lost! Can't see that the formula works. No file from Terranoman? Epinn "PapaDos" wrote in message ... =SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 ) Adjust to your ranges and Drag-fill as needed. -- Festina Lente "Terranoman" wrote: I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
If you need to get multiple hits, then this one should do it:
=SUMPRODUCT( ( LEN( B1 ) - LEN( SUBSTITUTE( LOWER( B1 ), LOWER( $IU$1:$IU$50 ), "" ) ) ) / LEN( $IU$1:$IU$50 ), $IV$1:$IV$50 ) -- Festina Lente "Roger Govier" wrote: Hi PapaDos Very clever thinking. I like it!! However, if there should be a repeat of items in cell B2, then it would not get picked up for the second (or subsequent) occurrence. Bob's formula does pick up multiple occurrences. I have no idea whether the OP's data has single or multiple occurrences of items. I still have not received any file from him / her. -- Regards Roger Govier "PapaDos" wrote in message ... LOL, it does work. I simply approach it the other way around. Instead of looking for extracted parts of the string into a table, I look for the table column in the string... -- Festina Lente "Epinn" wrote: An example of column B contents is ah, zy, pd, (in a single cell on each row) =SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 ) I am totally lost! Can't see that the formula works. No file from Terranoman? Epinn "PapaDos" wrote in message ... =SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 ) Adjust to your ranges and Drag-fill as needed. -- Festina Lente "Terranoman" wrote: I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
Hi
If there are blank values within range IU1:IU50 then I get #DIV/0 errors. Array entering {=SUMPRODUCT( ( LEN( B1 ) - LEN( SUBSTITUTE( LOWER( B1 ) , LOWER( $IU$1:$IU$50 ), "" ) ) ) / MAX(LEN( $IU$1:$IU$50 ),1), $IV$1:$IV$50 )} seems to give the correct result though. -- Regards Roger Govier "PapaDos" wrote in message ... If you need to get multiple hits, then this one should do it: =SUMPRODUCT( ( LEN( B1 ) - LEN( SUBSTITUTE( LOWER( B1 ), LOWER( $IU$1:$IU$50 ), "" ) ) ) / LEN( $IU$1:$IU$50 ), $IV$1:$IV$50 ) -- Festina Lente "Roger Govier" wrote: Hi PapaDos Very clever thinking. I like it!! However, if there should be a repeat of items in cell B2, then it would not get picked up for the second (or subsequent) occurrence. Bob's formula does pick up multiple occurrences. I have no idea whether the OP's data has single or multiple occurrences of items. I still have not received any file from him / her. -- Regards Roger Govier "PapaDos" wrote in message ... LOL, it does work. I simply approach it the other way around. Instead of looking for extracted parts of the string into a table, I look for the table column in the string... -- Festina Lente "Epinn" wrote: An example of column B contents is ah, zy, pd, (in a single cell on each row) =SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 ) I am totally lost! Can't see that the formula works. No file from Terranoman? Epinn "PapaDos" wrote in message ... =SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 ) Adjust to your ranges and Drag-fill as needed. -- Festina Lente "Terranoman" wrote: I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
Since we are dealing with fixed length strings (2), we don't need the LEN(
$IU$1:$IU$50 ) at all: =SUMPRODUCT( ( LEN( B1 ) - LEN( SUBSTITUTE( LOWER( B1 ), LOWER( $IU$1:$IU$50 ), "" ) ) ) / 2, $IV$1:$IV$50 ) I posted the other one to make it a bit more generic. Lookup tables should be a named range with no empty rows, so the #DIV/0 error is bothering me a lot less than having to use an array formula... In any case, this one should work OK in most situations: =SUMPRODUCT( ( LEN( B1) - LEN( SUBSTITUTE( LOWER( B1), LOWER( $IU$1:$IU$50 ), "" ) ) ) / ( LEN( $IU$1:$IU$50 ) + ( LEN( $IU$1:$IU$50 ) = 0 ) ), $IV$1:$IV$50 ) -- Festina Lente "Roger Govier" wrote: Hi If there are blank values within range IU1:IU50 then I get #DIV/0 errors. Array entering {=SUMPRODUCT( ( LEN( B1 ) - LEN( SUBSTITUTE( LOWER( B1 ) , LOWER( $IU$1:$IU$50 ), "" ) ) ) / MAX(LEN( $IU$1:$IU$50 ),1), $IV$1:$IV$50 )} seems to give the correct result though. -- Regards Roger Govier "PapaDos" wrote in message ... If you need to get multiple hits, then this one should do it: =SUMPRODUCT( ( LEN( B1 ) - LEN( SUBSTITUTE( LOWER( B1 ), LOWER( $IU$1:$IU$50 ), "" ) ) ) / LEN( $IU$1:$IU$50 ), $IV$1:$IV$50 ) -- Festina Lente "Roger Govier" wrote: Hi PapaDos Very clever thinking. I like it!! However, if there should be a repeat of items in cell B2, then it would not get picked up for the second (or subsequent) occurrence. Bob's formula does pick up multiple occurrences. I have no idea whether the OP's data has single or multiple occurrences of items. I still have not received any file from him / her. -- Regards Roger Govier "PapaDos" wrote in message ... LOL, it does work. I simply approach it the other way around. Instead of looking for extracted parts of the string into a table, I look for the table column in the string... -- Festina Lente "Epinn" wrote: An example of column B contents is ah, zy, pd, (in a single cell on each row) =SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 ) I am totally lost! Can't see that the formula works. No file from Terranoman? Epinn "PapaDos" wrote in message ... =SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 ) Adjust to your ranges and Drag-fill as needed. -- Festina Lente "Terranoman" wrote: I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
Very nice.
Both work admirably, and I agree far better to avoid array entries. -- Regards Roger Govier "PapaDos" wrote in message ... Since we are dealing with fixed length strings (2), we don't need the LEN( $IU$1:$IU$50 ) at all: =SUMPRODUCT( ( LEN( B1 ) - LEN( SUBSTITUTE( LOWER( B1 ), LOWER( $IU$1:$IU$50 ), "" ) ) ) / 2, $IV$1:$IV$50 ) I posted the other one to make it a bit more generic. Lookup tables should be a named range with no empty rows, so the #DIV/0 error is bothering me a lot less than having to use an array formula... In any case, this one should work OK in most situations: =SUMPRODUCT( ( LEN( B1) - LEN( SUBSTITUTE( LOWER( B1), LOWER( $IU$1:$IU$50 ), "" ) ) ) / ( LEN( $IU$1:$IU$50 ) + ( LEN( $IU$1:$IU$50 ) = 0 ) ), $IV$1:$IV$50 ) -- Festina Lente "Roger Govier" wrote: Hi If there are blank values within range IU1:IU50 then I get #DIV/0 errors. Array entering {=SUMPRODUCT( ( LEN( B1 ) - LEN( SUBSTITUTE( LOWER( B1 ) , LOWER( $IU$1:$IU$50 ), "" ) ) ) / MAX(LEN( $IU$1:$IU$50 ),1), $IV$1:$IV$50 )} seems to give the correct result though. -- Regards Roger Govier "PapaDos" wrote in message ... If you need to get multiple hits, then this one should do it: =SUMPRODUCT( ( LEN( B1 ) - LEN( SUBSTITUTE( LOWER( B1 ), LOWER( $IU$1:$IU$50 ), "" ) ) ) / LEN( $IU$1:$IU$50 ), $IV$1:$IV$50 ) -- Festina Lente "Roger Govier" wrote: Hi PapaDos Very clever thinking. I like it!! However, if there should be a repeat of items in cell B2, then it would not get picked up for the second (or subsequent) occurrence. Bob's formula does pick up multiple occurrences. I have no idea whether the OP's data has single or multiple occurrences of items. I still have not received any file from him / her. -- Regards Roger Govier "PapaDos" wrote in message ... LOL, it does work. I simply approach it the other way around. Instead of looking for extracted parts of the string into a table, I look for the table column in the string... -- Festina Lente "Epinn" wrote: An example of column B contents is ah, zy, pd, (in a single cell on each row) =SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 ) I am totally lost! Can't see that the formula works. No file from Terranoman? Epinn "PapaDos" wrote in message ... =SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 ) Adjust to your ranges and Drag-fill as needed. -- Festina Lente "Terranoman" wrote: I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
SUMIF or SUMPRODUCT to total cells containing multiple texts
The formula is working brilliantly....thankyou everyone. I appreciate the
time you've all spent on this. "Roger Govier" wrote: Very nice. Both work admirably, and I agree far better to avoid array entries. -- Regards Roger Govier "PapaDos" wrote in message ... Since we are dealing with fixed length strings (2), we don't need the LEN( $IU$1:$IU$50 ) at all: =SUMPRODUCT( ( LEN( B1 ) - LEN( SUBSTITUTE( LOWER( B1 ), LOWER( $IU$1:$IU$50 ), "" ) ) ) / 2, $IV$1:$IV$50 ) I posted the other one to make it a bit more generic. Lookup tables should be a named range with no empty rows, so the #DIV/0 error is bothering me a lot less than having to use an array formula... In any case, this one should work OK in most situations: =SUMPRODUCT( ( LEN( B1) - LEN( SUBSTITUTE( LOWER( B1), LOWER( $IU$1:$IU$50 ), "" ) ) ) / ( LEN( $IU$1:$IU$50 ) + ( LEN( $IU$1:$IU$50 ) = 0 ) ), $IV$1:$IV$50 ) -- Festina Lente "Roger Govier" wrote: Hi If there are blank values within range IU1:IU50 then I get #DIV/0 errors. Array entering {=SUMPRODUCT( ( LEN( B1 ) - LEN( SUBSTITUTE( LOWER( B1 ) , LOWER( $IU$1:$IU$50 ), "" ) ) ) / MAX(LEN( $IU$1:$IU$50 ),1), $IV$1:$IV$50 )} seems to give the correct result though. -- Regards Roger Govier "PapaDos" wrote in message ... If you need to get multiple hits, then this one should do it: =SUMPRODUCT( ( LEN( B1 ) - LEN( SUBSTITUTE( LOWER( B1 ), LOWER( $IU$1:$IU$50 ), "" ) ) ) / LEN( $IU$1:$IU$50 ), $IV$1:$IV$50 ) -- Festina Lente "Roger Govier" wrote: Hi PapaDos Very clever thinking. I like it!! However, if there should be a repeat of items in cell B2, then it would not get picked up for the second (or subsequent) occurrence. Bob's formula does pick up multiple occurrences. I have no idea whether the OP's data has single or multiple occurrences of items. I still have not received any file from him / her. -- Regards Roger Govier "PapaDos" wrote in message ... LOL, it does work. I simply approach it the other way around. Instead of looking for extracted parts of the string into a table, I look for the table column in the string... -- Festina Lente "Epinn" wrote: An example of column B contents is ah, zy, pd, (in a single cell on each row) =SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 ) I am totally lost! Can't see that the formula works. No file from Terranoman? Epinn "PapaDos" wrote in message ... =SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 ) Adjust to your ranges and Drag-fill as needed. -- Festina Lente "Terranoman" wrote: I am attempting to set up a spreadsheet whereby I can summarise the weight load of a vehicle. The cells will contain a variation of abbreviated texts in the same cell e.g. An example of column B contents is ah, zy, pd, (in a single cell on each row) Each abbreviation will have it's own unique number (weight) allocated in two seperate column which the formula will refer to - i.e Column IU IV Row 1 ah 2.2 Row 2 zy 3.7 Row 3 pd 1.4 There will be approximately 50 such text references and values. Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in a single cell). Is it possible to 'find' the idividual text abbreviation (ah, zy or pd) in column B and total the allocated weight using sumif or sumproduct even though they are in the same cell? Any suggestions greatly appreciated. |
| All times are GMT +1. The time now is 01:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com