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 |
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 |