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

 

[henrim]

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.

That didn't work. I've already set my template so can't change it now. Thanks

 

[Aladin Akyurek]

That didn't work.

That's surprising...

I've already set my template so can't change it now. Thanks

Ok. I'd have tried the concatenation method though if performance intolerably slow.

 

[henrim]

That's surprising...



Ok. I'd have tried the concatenation method though if performance intolerably slow.

I don't fully understand what you concatenation method. Perhaps an example might help.

The natural evolution of the formula was to create multiple conditions activatin/deactive as desired. This is what I currently have as the formula:
=SUMPRODUCT((Building=IF($B16="",Building,$B16))*(Unit_Class=IF($C16="",Unit_Class,$C16))*(Unit_Type=IF($D16="",Unit_Type,$D16)),Rent_Month_Total)

My sheet is only a template not completely populated and it's getting slow (I use sumproduct almost everywhere). I fear once my sheet is populated it'll be unusable beoynd repair.

As per your suggestion above I changed the fomrulae to this:

=SUMIFS(Rent_Month_Total,Building,IF($B16="",Building,$B16),Unit_Class,IF($C16="",Unit_Class,$C16),Unit_Type,IF($D16="",Unit_Type,$D16))

Unfortunately, this formula provides simply produces it a zero.

I also tried warpping the above formula as into a sumproduct (thought I belive that was a type on your end, and why use sumproduct if were trying to get away from it?). regardless, i tried the folloinwg:

=sumproduct(SUMIFS(Rent_Month_Total,Building,IF($B16="",Building,$B16),Unit_Class,IF($C16="",Unit_Class,$C16),Unit_Type,IF($D16="",Unit_Type,$D16)))

Please let me know if I can provide you with more details.
This produced an bizzare and incorrect anser.

 

[Aladin Akyurek]

I don't fully understand what you concatenation method. Perhaps an example might help.

The natural evolution of the formula was to create multiple conditions activatin/deactive as desired. This is what I currently have as the formula:
=SUMPRODUCT((Building=IF($B16="",Building,$B16))*(Unit_Class=IF($C16="",Unit_Class,$C16))*(Unit_Type=IF($D16="",Unit_Type,$D16)),Rent_Month_Total)

My sheet is only a template not completely populated and it's getting slow (I use sumproduct almost everywhere). I fear once my sheet is populated it'll be unusable beoynd repair.

As per your suggestion above I changed the fomrulae to this:

=SUMIFS(Rent_Month_Total,Building,IF($B16="",Building,$B16),Unit_Class,IF($C16="",Unit_Class,$C16),Unit_Type,IF($D16="",Unit_Type,$D16))

Unfortunately, this formula provides simply produces it a zero.

I also tried warpping the above formula as into a sumproduct (thought I belive that was a type on your end, and why use sumproduct if were trying to get away from it?). regardless, i tried the folloinwg:

=sumproduct(SUMIFS(Rent_Month_Total,Building,IF($B16="",Building,$B16),Unit_Class,IF($C16="",Unit_Class,$C16),Unit_Type,IF($D16="",Unit_Type,$D16)))

Please let me know if I can provide you with more details.
This produced an bizzare and incorrect anser.

Using IF with more than one condition creates multiple row vectors while SumIfs must have in alternate forms: row vectopr, column vector, row vector, etc. Hence the incorrect answer.

The concatenation method involves concatenating individual values of two or more columns: Example:

Entered in G2

=A2&"|"&B2&"|"&C2

and copied down.

This allows me something like:

=SUMIF($G$2:$G$100,J2&"|"&K2&"|"&L2,$D$2:$D$100)

instead of:

=SUMPRODUCT(
  --($A$2:$A$100=J2),
  --($B$2:$B$100=K2),
  --($C$2:$C$100=L2),
  $D$2:$D$100)

Or...

=SUMIF($G:$G$100,J2&"|"&"*"&"|"&L2,$D$2:$D$100)

instead of:

=SUMPRODUCT(
  --($A$2:$A$100=J2),
  --($B$2:$B$100=IF(K2="",K2,$B$2:$B$100)),
  --($C$2:$C$100=L2),
  $D$2:$D$100)