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
|