Tricky: Sum if either column contains value AND third column contains EITHER of two values

[omahacrab]

Hi guys

In the table below, I want to add the values in row D for when EITHER rows A or B contain "Jill" AND row C contains EITHER "Floor" or "Counter".

The correct answer should be 5.65 but I really can't figure this formula out - everything I try gives me an error. Any ideas anybody?

A	B	C	D
Jill	Jay	Floor	1.3
Jane	Jill	Counter	1.3
Jay	Jane	Floor	1.05
Jill	Joe	Counter	1.7
Jay	Jill	Floor	1.35
Jane	Jay	Counter	1.3
Jill	Joe	Stop	2.1

[e.isayev]

=SUMPRODUCT(((A1:A7="Jill")+(B1:B7="Jill"))*(C1:C7="Floor")*(D1:D7))+SUMPRODUCT(((A1:A7="Jill")+(B1:B7="Jill"))*(C1:C7="Counter")*(D1:D7))

[benishiryo]

hi omahacrab, how about:
=SUMPRODUCT((A1:A7="Jill")*(C1:C7={"Floor","Counter"})*(D1:D7))+SUMPRODUCT((B1:B7="Jill")*(C1:C7={"Floor","Counter"})*(D1:D7))

[Pepe Le Mokko]

A little shorter

Please Login or Register  to view this content.

[omahacrab]

All 3 solutions work perfectly thank you all indeed! Need to start getting better at this stuff.

[Pepe Le Mokko]

You can find a very nice explanation on SUMPRODUCT at this page

[omahacrab]

Thanks a lot Pepe

[FDibbins]

this is kinda cheating, but I added a helper column and then used this, copied down. sum the results and you get 5.65...
=IF(AND(OR(A1="Jill",B1="Jill"),OR(C1="Floor",C1="Counter")),D1,"")

Edit: I can never get the sumproduct to work lol

[omahacrab]

Will give this one a try too thanks

[Teethless mama]

A little shorter

Please Login or Register  to view this content.

A little more shorter...

=SUMPRODUCT(((A1:A7="jill")+(B1:B7="jill"))*(C1:C7={"floor","counter"})*D1:D7)