Sumproduct if

[Tiina]

Hi,
I need to combine SUMPRODUCT and IF statements. I searched prior posts and found this formula as an example. IT WORKS!! Thank you!


=SUMPRODUCT(--($J$14:$J$33="service"),$K$14:$K$33,$Q$14:$Q$33)

But now I need to add an additional IF statement:

($J$14:$J$33="service") AND ($J$14:$J$33<>"xyz")

How do I achieve this?

Thank you!!
Tiina

 

[Marcelo Branco]

Hi Tina,

This second condition seems to me unnecessary, because if any J is equal to "service" it is also different from "xyz".

M.

 

[oldbrewer]

how can j14 = service and xyz ? do you want to add service and xyz rows - what is in columns K and Q ?

 

[Tiina]

Sorry, I forgot to change the range. It is now in column B. Basically, I just want to know how to add conditions to sumproduct formula
Thanks
T

($J$14:$J$33="service") AND ($B$14:$B$33<>"xyz")

 

[Marcelo Branco]

Try

=SUMPRODUCT(--($B$14:$B$33<>"xyz"),--($J$14:$J$33="service"),$K$14:$K$33,$Q$14:$Q$33)

M.

 

[oldbrewer]

=sumproduct(($J$14:$J$33="service")*($B$14:$B$33<>"xyz")*($k$14:$k$33))

assuming values are in column K

 

[Tiina]

Thanks, it worked

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.