Multiple Conditional IF with the ability activate/deactivate a condition

[henrim]

Excel 2007

How do I make a multiple conditional formula have the ability to active/deactivate a certain condition dynamically.
The example below should clarify; I have the following formula:

=SUMPRODUCT((Building=$B15)*(Unit_Type=$B16)*(Unit_Line=H15),Rent_Month_Total)

This formula sums the total rent per month based on the Building, Unit_Type, and Unit Line (which are all defined ranges) I specify in cells B15, B16, and H15. However, in certain instances I need to deactivate the (Unit_Line=H15) criteria; essentially, I need to sum only the rent based on Building and Unit_Type and not Unit_Line (most common reason for this is because I don't have Unit_Line info).

My line of thinking to this problem was to create an 'if" condition for the condition I'd like to have the ability to turn off. I created an 'if' condition where 0 is "off" and not 0 is "on" (see red highlighted section of formula below). So, when the 'if' formula is false (or off) it simply produces a 1 which when entered into the sumproduct array does not do anything and essentially deactivates the condition. However, I'm having a difficult time getting the "on" part working, and activating the condition when I need it. My formula is shown below.

=SUMPRODUCT((Building=$B15)*(Unit_Type=$B16)*IF($I15<>0,"("&Unit_Line=$H15&")",1),Rent_Month_Total)

The I15 cell is the toggle that allows activating or deactivating the cell.

What am I doing wrong; why can't I activate the condition when needed? By the way, when I do active the condition the formula simple produces a zero. At least I'm not getting an error.

Also, I'm aware I can achieve the same thing with the 'sumifs' function, but I thought it would be easier with sumproduct as the '*' joiner makes it a bit cleaner when dealing with formula. However, if someone can achieve the same thing with the sumifs formula I'll use that in heartbeat since I think the sumifs are faster.

Thank you in advance.
-Henri

 

[Aladin Akyurek]

Try...

Put in the native comma syntax:

=SUMPRODUCT(
     --(Building=$B15)
     --(Unit_Type=$B16)'
     --(Unit_Line=IF(H15="",Unit_Line,H15)),
     Rent_Month_Total)

Leave H15 empty if it's not to be considered.

 

[henrim]

I made the suggested change without success unfortunately.
Please note the I15 cell activates/deactivates the condition. The H15 cell is the condition of the Unit_Line when it is activated. I've corrected your suggested formula accordingly.

Below is the edited formula to ensure we're all on the same page.

SUMPRODUCT(Building=$B15,Unit_Type=$B16,Unit_Line=IF($I15="",Unit_Line,$H15),Rent_Month_Total)

What's next?

 

[Aladin Akyurek]

I made the suggested change without success unfortunately.
Please note the I15 cell activates/deactivates the condition. The H15 cell is the condition of the Unit_Line when it is activated. I've corrected your suggested formula accordingly.

Below is the edited formula to ensure we're all on the same page.

SUMPRODUCT(Building=$B15,Unit_Type=$B16,Unit_Line=IF($I15="",Unit_Line,$H15),Rent_Month_Total)

What's next?

Is it not possible for you to leave h15 empty when you don't want to evaluate (or when you want to deactivate) Unit_Line?

 

[henrim]

Is it not possible for you to leave h15 empty when you don't want to evaluate (or when you want to deactivate) Unit_Line?

Yes H15 can be empty when I do not want to evaluate Unit_Line.

Also, I just tried your original formula and I'm still getting zero.

 

[Aladin Akyurek]

Yes H15 can be empty when I do not want to evaluate Unit_Line.

Also, I just tried your original formula and I'm still getting zero.

Did you omit the -- bits frm the formula? If so, re-add them...

=SUMPRODUCT(
--(Building=$B15),
--(Unit_Type=$B16),
--(Unit_Line=IF(H15="",Unit_Line,H15)),
Rent_Month_Total)

 

[henrim]

It worked. Thank you!

For everyone else this is the final formula that allows activating/deactivating a condtion:
SUMPRODUCT(--(Building=$B15),--(Unit_Type=$B16),--(Unit_Line=IF(H15="",Unit_Line,H15)),Rent_Month_Total)

Also, much more elegant than mine because it reduces the need for the I15 cell to act as a switch.

If you can, for my own learning, what do the "--" in front of the parentheses do?

 

[Aladin Akyurek]

It worked. Thank you!

You are welcome. Thanks for providing feedback.

For everyone else this is the final formula that allows activating/deactivating a condtion:
SUMPRODUCT(--(Building=$B15),--(Unit_Type=$B16),--(Unit_Line=IF(H15="",Unit_Line,H15)),Rent_Month_Total)

Also, much more elegant than mine because it reduces the need for the I15 cell to act as a switch.

If you can, for my own learning, what do the "--" in front of the parentheses do?

The -- bit acts the same as the in-between * in that they convert the true/ false evaluations into 1/0 evaluations SumProduct needs.

 

[henrim]

Can this be achieved with 'sumifs'? All the 'sumproduts' are really slowing down my sheet

Thank you.

 

[Aladin Akyurek]

Can this be achieved with 'sumifs'? All the 'sumproduts' are really slowing down my sheet

Thank you.

Sort of... It might be worth trying the following:

=SUMPRODUCT(
    SUMIFS(
      Rent_Month_Total,
      Building,$B15,
      Unit_Type,$B16,
      Unit_Line,IF(H15="",Unit_Line,H15)))

Another option is to reduce the number of conditional terms in SumProduct if all of the conditional terms are equal tests by concatenating some of the relevant ranges into one. For this option, we need the relevant ranges the range names refer to.