"Either or" condition in SUMPRODUCT

[cichli_04]

Hi Forum, new member here

My current formula is

=SUMPRODUCT((I7:N7="Completed")*(I6:N6))

whereby every time Excel sees the status "Completed" in any of the cells of the I7:N7 range, it multiplies it with its corresponding weight (a constant number that I have set) in the I6:N6 range, and then calculates the Sumproduct.

What I want now is to change it so it proceeds with the multiplication if it detects either the status "Completed" or the status "Not Applicable" but I haven't been able to make it work so far. Is this possible with SUMPRODUCT or I'll have to change the formula?

Thank you!

 

[RoryA]

Welcome to the forum. You should be able to use either:

=SUMPRODUCT(((I7:N7="Completed")+(I7:N7="Not Applicable"))*(I6:N6))

or:

=SUMPRODUCT((I7:N7={"Completed","Not Applicable"})*(I6:N6))

 

[cichli_04]

Welcome to the forum. You should be able to use either:

=SUMPRODUCT(((I7:N7="Completed")+(I7:N7="Not Applicable"))*(I6:N6))

or:

=SUMPRODUCT((I7:N7={"Completed","Not Applicable"})*(I6:N6))

Many thanks Rory, both worked!