Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumProduct with options
Hello,
I would like to be able to sum numbers in column I based on conditions in A,B,C but I would like to make the condition in each column optional, so I could use the conditions in A and B only for instance (or in any combination) The formula below works without the optio =SUMPRODUCT(($A$112:$A$1155=$A$14)*($C$112:$C$1155 =$C$14)*($B$112:$B$1155=$B$14)*(I$112:I$1155)) How can I add something like if($A$13=true,($A$112:$A$1155=$A$14), etc in the SUMPRODUCT formula? Any help is appreciated.thanks -- caroline |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumProduct with options
You can wrap the conditionals up within an IF function.
=SUMPRODUCT(--IF(A1:A10="",TRUE, (A1:A10=2)),--IF(B1:B10="",TRUE,(B1:B10="b")),C1:C10) This formula will return the SUM of values in C1:C10 where A1:A10 is either empty or 2, and B1:B10 is empty or 'b'. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "caroline" wrote in message ... Hello, I would like to be able to sum numbers in column I based on conditions in A,B,C but I would like to make the condition in each column optional, so I could use the conditions in A and B only for instance (or in any combination) The formula below works without the option =SUMPRODUCT(($A$112:$A$1155=$A$14)*($C$112:$C$1155 =$C$14)*($B$112:$B$1155=$B$14)*(I$112:I$1155)) How can I add something like if($A$13=true,($A$112:$A$1155=$A$14), etc in the SUMPRODUCT formula? Any help is appreciated.thanks -- caroline |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumProduct - checking for multiple options in one column | Excel Discussion (Misc queries) | |||
Using same fomula to count AND and OR options / sumproduct | Excel Worksheet Functions | |||
Send mail with outlook message options-Delivery OptionS | Excel Programming | |||
Working with options from within Tools Options clears the Clipboar | Excel Programming | |||
How to diasble the 'Tools - Options - View - Comments' options? | Excel Programming |