sumproduct contains

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Woodi2 - 13 Sep 2007 23:46 GMT

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 to this Message

Teethless mama - 13 Sep 2007 23:52 GMT

=SUMIF(G1:G6,"*Tesco*",H1:H6)

> 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
[quoted text clipped - 15 lines]
> not recognise Tesco when another word appears in the cell.
> Thanks

Reply to this Message

Woodi2 - 14 Sep 2007 00:04 GMT

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.

> =SUMIF(G1:G6,"*Tesco*",H1:H6)
>
[quoted text clipped - 17 lines]
> > not recognise Tesco when another word appears in the cell.
> > Thanks

Reply to this Message

Bob Phillips - 14 Sep 2007 00:13 GMT

I hope that you mean Tesco OR Spar

=SUMPRODUCT(--((ISNUMBER(FIND("Tesco",G1:G6)))+(ISNUMBER(FIND("Spar",G1:G6)))),H1:H6)

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Thanks for the reply, quick I might add. I agree with what you suggest
> but I
[quoted text clipped - 34 lines]
>> > not recognise Tesco when another word appears in the cell.
>> > Thanks

Reply to this Message

Teethless mama - 14 Sep 2007 00:04 GMT

If you preferred SUMPRODUCT function then try this:

=SUMPRODUCT(--ISNUMBER(SEARCH("Tesco",G1:G6)),H1:H6)

Reply to this Message

Woodi2 - 14 Sep 2007 01:28 GMT

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.

> If you preferred SUMPRODUCT function then try this:
>
[quoted text clipped - 19 lines]
> > not recognise Tesco when another word appears in the cell.
> > Thanks

Reply to this Message

Bob Phillips - 14 Sep 2007 09:27 GMT

see my earlier reply

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Thanks again. I dont know if I'm making this too complicated for myself
> now,
[quoted text clipped - 31 lines]
>> > not recognise Tesco when another word appears in the cell.
>> > Thanks

Reply to this Message