Sumproduct using and, and or

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Back
Top