Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct contains
I am trying to evalute all cells in a column with the word Tesco in. The
problem I have is when I download the data from an external range, the cell that contains Tesco can have other words in it, i.e. Card Payment to Tesco or Tesco Store. I can see how to use SumProduct if the cell only contained Tesco but not with any other words. I have used the SumIF function as follows =SUMIF($G1,"*TESCO STORE*",$H1) wherby adding the * after the " allows for varaitions, this however has to be repeated for every row. As an example I have the following G H 1 Tesco Store 5 2 Spar 15 3 Shell 1 4 Tesco 12 5 Tesco 6 6 Smiths 15 I have the following Formula =SUMPRODUCT((G1:G6="Tesco")*(H1:H6)) this returns an answer of 18. The answer I expect is 23. As you can see it does not recognise Tesco when another word appears in the cell. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct contains
=SUMIF(G1:G6,"*Tesco*",H1:H6)
"Woodi2" wrote: I am trying to evalute all cells in a column with the word Tesco in. The problem I have is when I download the data from an external range, the cell that contains Tesco can have other words in it, i.e. Card Payment to Tesco or Tesco Store. I can see how to use SumProduct if the cell only contained Tesco but not with any other words. I have used the SumIF function as follows =SUMIF($G1,"*TESCO STORE*",$H1) wherby adding the * after the " allows for varaitions, this however has to be repeated for every row. As an example I have the following G H 1 Tesco Store 5 2 Spar 15 3 Shell 1 4 Tesco 12 5 Tesco 6 6 Smiths 15 I have the following Formula =SUMPRODUCT((G1:G6="Tesco")*(H1:H6)) this returns an answer of 18. The answer I expect is 23. As you can see it does not recognise Tesco when another word appears in the cell. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct contains
Thanks for the reply, quick I might add. I agree with what you suggest but I
probably didnt make myself clear. I'm new to SumProduct so I'm just learning. I can use the sumif OK, but what I would like to sum is all cells that relate to grocery shops. i.e. Teso AND Spar. I've tried this but not too sure what I'm doing =SUMPRODUCT((G1:G6="Tesco"),(G1:G6="Spar")*(H1:H6) ) as this returns zero. "Teethless mama" wrote: =SUMIF(G1:G6,"*Tesco*",H1:H6) "Woodi2" wrote: I am trying to evalute all cells in a column with the word Tesco in. The problem I have is when I download the data from an external range, the cell that contains Tesco can have other words in it, i.e. Card Payment to Tesco or Tesco Store. I can see how to use SumProduct if the cell only contained Tesco but not with any other words. I have used the SumIF function as follows =SUMIF($G1,"*TESCO STORE*",$H1) wherby adding the * after the " allows for varaitions, this however has to be repeated for every row. As an example I have the following G H 1 Tesco Store 5 2 Spar 15 3 Shell 1 4 Tesco 12 5 Tesco 6 6 Smiths 15 I have the following Formula =SUMPRODUCT((G1:G6="Tesco")*(H1:H6)) this returns an answer of 18. The answer I expect is 23. As you can see it does not recognise Tesco when another word appears in the cell. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct contains
If you preferred SUMPRODUCT function then try this:
=SUMPRODUCT(--ISNUMBER(SEARCH("Tesco",G1:G6)),H1:H6) "Woodi2" wrote: I am trying to evalute all cells in a column with the word Tesco in. The problem I have is when I download the data from an external range, the cell that contains Tesco can have other words in it, i.e. Card Payment to Tesco or Tesco Store. I can see how to use SumProduct if the cell only contained Tesco but not with any other words. I have used the SumIF function as follows =SUMIF($G1,"*TESCO STORE*",$H1) wherby adding the * after the " allows for varaitions, this however has to be repeated for every row. As an example I have the following G H 1 Tesco Store 5 2 Spar 15 3 Shell 1 4 Tesco 12 5 Tesco 6 6 Smiths 15 I have the following Formula =SUMPRODUCT((G1:G6="Tesco")*(H1:H6)) this returns an answer of 18. The answer I expect is 23. As you can see it does not recognise Tesco when another word appears in the cell. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct contains
I hope that you mean Tesco OR Spar
=SUMPRODUCT(--((ISNUMBER(FIND("Tesco",G1:G6)))+(ISNUMBER(FIND("S par",G1:G6)))),H1:H6) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Woodi2" wrote in message ... Thanks for the reply, quick I might add. I agree with what you suggest but I probably didnt make myself clear. I'm new to SumProduct so I'm just learning. I can use the sumif OK, but what I would like to sum is all cells that relate to grocery shops. i.e. Teso AND Spar. I've tried this but not too sure what I'm doing =SUMPRODUCT((G1:G6="Tesco"),(G1:G6="Spar")*(H1:H6) ) as this returns zero. "Teethless mama" wrote: =SUMIF(G1:G6,"*Tesco*",H1:H6) "Woodi2" wrote: I am trying to evalute all cells in a column with the word Tesco in. The problem I have is when I download the data from an external range, the cell that contains Tesco can have other words in it, i.e. Card Payment to Tesco or Tesco Store. I can see how to use SumProduct if the cell only contained Tesco but not with any other words. I have used the SumIF function as follows =SUMIF($G1,"*TESCO STORE*",$H1) wherby adding the * after the " allows for varaitions, this however has to be repeated for every row. As an example I have the following G H 1 Tesco Store 5 2 Spar 15 3 Shell 1 4 Tesco 12 5 Tesco 6 6 Smiths 15 I have the following Formula =SUMPRODUCT((G1:G6="Tesco")*(H1:H6)) this returns an answer of 18. The answer I expect is 23. As you can see it does not recognise Tesco when another word appears in the cell. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct contains
Thanks again. I dont know if I'm making this too complicated for myself now,
but, the actual table I have can have up to 2 different stores that could come under the grocery banner. Not sure if its getting a bit complicated. Any further advice would be much appreciated. "Teethless mama" wrote: If you preferred SUMPRODUCT function then try this: =SUMPRODUCT(--ISNUMBER(SEARCH("Tesco",G1:G6)),H1:H6) "Woodi2" wrote: I am trying to evalute all cells in a column with the word Tesco in. The problem I have is when I download the data from an external range, the cell that contains Tesco can have other words in it, i.e. Card Payment to Tesco or Tesco Store. I can see how to use SumProduct if the cell only contained Tesco but not with any other words. I have used the SumIF function as follows =SUMIF($G1,"*TESCO STORE*",$H1) wherby adding the * after the " allows for varaitions, this however has to be repeated for every row. As an example I have the following G H 1 Tesco Store 5 2 Spar 15 3 Shell 1 4 Tesco 12 5 Tesco 6 6 Smiths 15 I have the following Formula =SUMPRODUCT((G1:G6="Tesco")*(H1:H6)) this returns an answer of 18. The answer I expect is 23. As you can see it does not recognise Tesco when another word appears in the cell. Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct contains
see my earlier reply
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Woodi2" wrote in message ... Thanks again. I dont know if I'm making this too complicated for myself now, but, the actual table I have can have up to 2 different stores that could come under the grocery banner. Not sure if its getting a bit complicated. Any further advice would be much appreciated. "Teethless mama" wrote: If you preferred SUMPRODUCT function then try this: =SUMPRODUCT(--ISNUMBER(SEARCH("Tesco",G1:G6)),H1:H6) "Woodi2" wrote: I am trying to evalute all cells in a column with the word Tesco in. The problem I have is when I download the data from an external range, the cell that contains Tesco can have other words in it, i.e. Card Payment to Tesco or Tesco Store. I can see how to use SumProduct if the cell only contained Tesco but not with any other words. I have used the SumIF function as follows =SUMIF($G1,"*TESCO STORE*",$H1) wherby adding the * after the " allows for varaitions, this however has to be repeated for every row. As an example I have the following G H 1 Tesco Store 5 2 Spar 15 3 Shell 1 4 Tesco 12 5 Tesco 6 6 Smiths 15 I have the following Formula =SUMPRODUCT((G1:G6="Tesco")*(H1:H6)) this returns an answer of 18. The answer I expect is 23. As you can see it does not recognise Tesco when another word appears in the cell. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct | Excel Discussion (Misc queries) | |||
using -- with SUMPRODUCT | Excel Discussion (Misc queries) | |||
sumproduct? | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions |