Sumproduct using and, and or

[Lori]

I'm trying to add the numbers in column C, based on multiple conditions in
column A (or statement) and conditions in column B (an and statement to
column A). In the example below, I want to add the values in Column C based
on Column A equaling either X or Z, and Column B equaling NA (result would be
7)

A B C
X NA 2
X IDDD 1
Y NA 3
Y IDDD 2
Z NA 5
Z IDDD 4

 

[Pete_UK]

Try this:

=SUMPRODUCT((A1:A10="X")*(B1:B10="NA")*(C1:C10)) +
SUMPRODUCT((A1:A10="Z")*(B1:B10="NA")*(C1:C10))

Or, you might want to try:

=SUMPRODUCT(((A1:A10="X") + (A1:A10="Z"))*(B1:B10="NA")*(C1:C10))

which is a bit shorter.

Hope this helps.

Pete

 

[Toppers]

=SUMPRODUCT((A2:A7={"X","Z"})*(B2:B7="NA")*(C2:C7))