Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct using and, and or
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct using and, and or
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 On Jul 25, 5:00 pm, Lori wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct using and, and or
=SUMPRODUCT((A2:A7={"X","Z"})*(B2:B7="NA")*(C2:C7) )
"Pete_UK" wrote: 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 On Jul 25, 5:00 pm, Lori wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct | Excel Discussion (Misc queries) | |||
sumproduct | Excel Discussion (Misc queries) | |||
sumproduct | Excel Worksheet Functions | |||
sumproduct? | Excel Worksheet Functions |