SumProduct with optional conditions
 

Hello,
I would like to be able to sum numbers in column I based on conditions in
A,B,C . The formula below works
=SUMPRODUCT(($A$112:$A$1155=$A$14)*($C$112:$C$1155 =$C$14)*($B$112:$B$1155=$B$14)*(I$112:I$1155))
Now I would like to make the condition in each column optional, so i can use
the same formula if I want to sum column I only conditions in A and B for
instance (ignoring C)
Something like if($A$13=true,($A$112:$A$1155=$A$14), etc
How can I combine this in one elegant formula?
Any help is appreciated.thanks
--
caroline

SumProduct with optional conditions
 

Well, you need some way to indicate which sections you want to include. I
used cells A13, B13, C13. These cells I am using will have a value of 1 or 0.
A 1 indicates you WANT to include this criteria, and a 0 means you want to
EXCLUDE this criteria. NOTE: If you exclude all criteria (A13,B13,and C13 are
all 0), it will error.

Otherwise, use the following formula, which is an array** formula (after
typing formula, press CTRL+Shift+Enter to commit the formula to the cell).

=SUMPRODUCT((IF($A$13=0,1,$A$112:$A$1155=$A$14))*( IF($B$13=0,1,$B$112:$B$1155=$B$14))*(IF($C$13=0,1, $C$112:$C$1155=$C$14)),(I$112:I$1155))

Hope this helps.
--
John C


"caroline" wrote:

Hello,
I would like to be able to sum numbers in column I based on conditions in
A,B,C . The formula below works
=SUMPRODUCT(($A$112:$A$1155=$A$14)*($C$112:$C$1155 =$C$14)*($B$112:$B$1155=$B$14)*(I$112:I$1155))
Now I would like to make the condition in each column optional, so i can use
the same formula if I want to sum column I only conditions in A and B for
instance (ignoring C)
Something like if($A$13=true,($A$112:$A$1155=$A$14), etc
How can I combine this in one elegant formula?
Any help is appreciated.thanks
--
caroline

SumProduct with optional conditions
 

Thanks it worked with the Array formula
--
caroline


"John C" wrote:

Well, you need some way to indicate which sections you want to include. I
used cells A13, B13, C13. These cells I am using will have a value of 1 or 0.
A 1 indicates you WANT to include this criteria, and a 0 means you want to
EXCLUDE this criteria. NOTE: If you exclude all criteria (A13,B13,and C13 are
all 0), it will error.

Otherwise, use the following formula, which is an array** formula (after
typing formula, press CTRL+Shift+Enter to commit the formula to the cell).

=SUMPRODUCT((IF($A$13=0,1,$A$112:$A$1155=$A$14))*( IF($B$13=0,1,$B$112:$B$1155=$B$14))*(IF($C$13=0,1, $C$112:$C$1155=$C$14)),(I$112:I$1155))

Hope this helps.
--
John C


"caroline" wrote:

Hello,
I would like to be able to sum numbers in column I based on conditions in
A,B,C . The formula below works
=SUMPRODUCT(($A$112:$A$1155=$A$14)*($C$112:$C$1155 =$C$14)*($B$112:$B$1155=$B$14)*(I$112:I$1155))
Now I would like to make the condition in each column optional, so i can use
the same formula if I want to sum column I only conditions in A and B for
instance (ignoring C)
Something like if($A$13=true,($A$112:$A$1155=$A$14), etc
How can I combine this in one elegant formula?
Any help is appreciated.thanks
--
caroline