looking up 3 values to generate an answer using Sumproduct

[fentiger79]

im trying to use the Sumproduct function to examine 3 columns of data.

In the attached example I want the formula to say if cell E5 is in column A and column C contains a Y then sum column B

So far I have the following =SUMPRODUCT(A4:A19=E6,C4:C19="Y",B4:B19)

[KOKOSEK]

Try:

Please Login or Register  to view this content.

accept with CSE

[fentiger79]

Still showing a value error

[KOKOSEK]

Still showing a value error

Did you accept formula with Ctrl+Shift+Enter? Have you got {} in formula bar?
Capture.JPG

[fentiger79]

Thank you its now working

[KOKOSEK]

You welcome.
If you happy with solution, please use Thread tool and mark thread as SOLVED.

[avk]

Try with

Please Login or Register  to view this content.

[fentiger79]

=SUMPRODUCT(IF((A4:A19=E6)*(C4:C19="Y")=1,B4:B19))

what if I wanted to search c4:c19 for a particular word. Lets say same or different?

[KOKOSEK]

You can 'filter' C as you wish, depends what kind of info you want to keep in there.

[FlameRetired]

Also

Please Login or Register  to view this content.

[fentiger79]

There are 4 sentences in that field and I want the sum product to calculate if there is a key word in there. do I just put "" around the word I want or will that only look for that specific word.

[KOKOSEK]

Also

Please Login or Register  to view this content.

wildcards (* for anything)

[fentiger79]

{=SUMPRODUCT(IF((A4:A19=E6)*(C4:C19="Same*")=1,B4:B19))}

Would this work? or is there an easier way to do it.

[KOKOSEK]

If you got in col C something like:

SameYankee
SameTankee
SameSomething
Sameanything

then should works.

[fentiger79]

I couldn't get it to work but I substituted the "same*" for the exact phrase in my data and it worked.

Thank you for your help.

[KOKOSEK]

If you put SAME* it works for phrases as in examples - all string which is starts with 'SAME', the rest of characters is 'hidden' under *, that's why SAME*.
Other example:
*SAME* for 'charactersSAMEcharacters' or 'beforeSAMEafter'
*SAME for 'charactersSAME' or 'beforeSAME'
SAME* for 'SAMEafter' or 'SAMEcharacters'

Anyway, happy to help. Check it out on internet about wildcards.