Help with =SUMPRODUCT

[parkermazk]

Hello,

I am trying to use the =SUMPRODUCT formula but it does not seem to be
working. Not sure what I am doing wrong.

Here is an example of the data:

Adams, Wendy OD-Apple 6
Adams, Wendy OD-Orange 2.5
Adams, Wendy JD-Lemon 3
Adams, Wendy JD-Apple 8
Arment, Shannon JD-Cherry 25
Arment, Shannon OD-Orange 4
Arment, Shannon OD-Apple 2
Arment, Shannon JD-Blueberry 4
Baca, Ralph JD-Kiwi 4
Baca, Ralph JD-Lemon 1.5
Baca, Ralph JD-Cherry 8.5
Baca, Ralph JD-Grape 4
Baca, Ralph OD-Banana 1
Baca, Ralph OD-Apple 5
Borlan, Priscilla JD-Lemon 4
Borlan, Priscilla JD-Grape 8
Borlan, Priscilla OD-Orange 4
Borlan, Priscilla OD-Kiwi 4

Here is the formula I am trying to use:

=SUMPRODUCT((Practice!A1:A18=A1)*(Practice!B1:B18="JD")*(Practice!C1:C18))

If whats in column A matches and column B has the letters "JD" in it then
sum up whats in column C.

I think my problem is that I am only searching for a portion of the text in
column B.

Can anyone help with this?

 

[David Biddulph]

You're looking for it to be equal to "JD", not a string containing "JD".

 

[Mike H]

Hi,

Try this

=SUMPRODUCT((Practice!A1:A18=A1)*(--ISNUMBER(SEARCH("JD",Practice!B1:B18))=1)*(Practice!C1:C18))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Domenic]

Try...

=SUMPRODUCT((Practice!A1:A18=A1)*(LEFT(Practice!B1:B18,2)="JD")*(Practice!C1:C18))

or

=SUMPRODUCT(--(Practice!A1:A18=A1),--(LEFT(Practice!B1:B18,2)="JD"),Practice!C1:C18)

 

[Mike H]

Hi,

I should have added that this bit of the formula ensures it will only count
JD at the start of the string

(--ISNUMBER(SEARCH("JD",Practice!B1:B18))=1)

If you want to count JD anywhere in the string you can use

(ISNUMBER(SEARCH("JD",Practice!B1:B18)))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.