How to consolidate a Large Formula

[Grimm127]

HI All you Gurus out there,

I need a bit of help. I am working on a report that I need to basically get a percentage of revenue. My formula is Dymanic using Index where I can drill down to the account and using a Indirect for the Tab based on the selection of Year.

Below is a formula that I need to sum the correct revenue. The Revenue I need to add at three segments Internal, External and Broker from the Whole. Is there a way I can make this smaller maybe making it into a function?

Another thought is I just create a Macro where it inserts the formula into the cell and then save as values....Thanks in advance for your suggestions.....

=SUMIFS(INDEX(INDIRECT("'"&$BR$3&"'!"&"$W:$AH"),0,N$6),INDIRECT("'"&$BR$3&"'!"&"$L:$L"),$B7,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$4),$BE$4,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$5),$BE$5,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$6),$BE$6,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$7),$BE$7,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$8),$BE$8)-SUMIFS(INDEX(INDIRECT("'"&$BR$3&"'!"&"$W:$AH"),0,N$6),INDIRECT("'"&$BR$3&"'!"&"$L:$L"),$B7,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$4),$BE$4,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$5),$BE$5,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$6),$BE$6,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$7),$BE$7,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$8),$BE$8,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,14),"Brk")+SUMIFS(INDEX(INDIRECT("'"&$BR$3&"'!"&"$W:$AH"),0,N$6),INDIRECT("'"&$BR$3&"'!"&"$L:$L"),$B8,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$4),$BE$4,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$5),$BE$5,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$6),$BE$6,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$7),$BE$7,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$8),$BE$8)

 

[jasonb75]

As a starting point, by creating a named range called rng that refers to =INDIRECT("'"&$BR$3&"'!"&"$A:$N") you could shorten the formula to

=SUMIFS(INDEX(INDIRECT("'"&$BR$3&"'!"&"$W:$AH"),0,N$6),INDIRECT("'"&$BR$3&"'!"&"$L:$L"),$B7,INDEX(rng,0,$BF$4),$BE$4,INDEX(rng,0,$BF$5),$BE$5,INDEX(rng,0,$BF$6),$BE$6,INDEX(rng,0,$BF$7),$BE$7,INDEX(rng,0,$BF$8),$BE$8)-SUMIFS(INDEX(INDIRECT("'"&$BR$3&"'!"&"$W:$AH"),0,N$6),INDIRECT("'"&$BR$3&"'!"&"$L:$L"),$B7,INDEX(rng,0,$BF$4),$BE$4,INDEX(rng,0,$BF$5),$BE$5,INDEX(rng,0,$BF$6),$BE$6,INDEX(rng,0,$BF$7),$BE$7,INDEX(rng,0,$BF$8),$BE$8,INDEX(rng,0,14),"Brk")+SUMIFS(INDEX(INDIRECT("'"&$BR$3&"'!"&"$W:$AH"),0,N$6),INDIRECT("'"&$BR$3&"'!"&"$L:$L"),$B8,INDEX(rng,0,$BF$4),$BE$4,INDEX(rng,0,$BF$5),$BE$5,INDEX(rng,0,$BF$6),$BE$6,INDEX(rng,0,$BF$7),$BE$7,INDEX(rng,0,$BF$8),$BE$8)

You could shorten it a little further by doing similar with the other indirect ranges.

 

[KRice]

Following @jasonb75's suggestion and naming three ranges:
rng1 =INDIRECT("'"&$BR$3&"'!"&"$A:$N")
rng2 =INDIRECT("'"&$BR$3&"'!"&"$W:$AH")
rng3 =INDIRECT("'"&$BR$3&"'!"&"$L:$L")

Then the 1st and 3rd SUMIF functions are very similar, differing in only one term. I believe they can be combined to form an equivalent SUMPRODUCT function that effectively establishes an OR condition on the rng3 array. I've moved that particular term to the end of the first SUMPRODUCT expression below where you'll see ((rng3=$B7)+(rng3=$B8)). The other SUMIFS terms (the 2nd term) in post #1 is also very similar. I've converted that to an equivalent (I think) SUMPRODUCT expression and placed it after a line break below (to better align the two terms for comparison), but I wonder if there might be some way to further consolidate these two terms. I have not tested this, but it may offer some hope...

= SUMPRODUCT(INDEX(rng2,0,N$6),(INDEX(rng1,0,$BF$4)=$BE$4)*(INDEX(rng1,0,$BF$5)=$BE$5)*(INDEX(rng1,0,$BF$6)=$BE$6)*(INDEX(rng1,0,$BF$7)=$BE$7)*(INDEX(rng1,0,$BF$8)=$BE$8)*((rng3=$B7)+(rng3=$B8)))
- SUMPRODUCT(INDEX(rng2,0,N$6),(INDEX(rng1,0,$BF$4)=$BE$4)*(INDEX(rng1,0,$BF$5)=$BE$5)*(INDEX(rng1,0,$BF$6)=$BE$6)*(INDEX(rng1,0,$BF$7)=$BE$7)*(INDEX(rng1,0,$BF$8)=$BE$8)*( rng3=$B7) * (INDEX(rng1,0,14)="Brk"))

 

[jasonb75]

Following the suggestion from @KRice who noticed things that I missed, I think that this should work if array confirmed with Ctrl Shift Enter.

=SUM(SUMIFS(INDEX(rng2,0,N$6),rng,$B7:$B8,INDEX(rng,0,$BF$4),$BE$4,INDEX(rng,0,$BF$5),$BE$5,INDEX(rng,0,$BF$6),$BE$6,INDEX(rng,0,$BF$7),$BE$7,INDEX(rng,0,$BF$8),$BE$8,INDEX(rng1,0,14,{"<>Brk";"*"})))

Note that I haven't tested this, so there is a chance that it might not behave as expected.

 

[KRice]

I had begun to go this route with rng,{$B7:$B8} in a SUMIF but ran into an issue as the array arguments need to be hard-wired rather than cell references, so I'm curious whether the "rng,$B7:$B8" works to return both sums when either condition is met. And then I noticed the minus sign in front of the 2nd SUMIF term and wasn't how to incorporate it. That SUMIF term looks identical to the first one, except it has one extra condition (the index(ref)="Brk" term) and the sign for the sum is negative.

 

[jasonb75]

so I'm curious whether the "rng,$B7:$B8" works to return both sums when either condition is met.

The method does work, but with the addition of the indirect ranges and everything else I could have quite easily messed up the syntax.
The main point that could be a fail is the array at the end, as we don't know what the data consists of, "*" might not pick everything up as intended, in hindsight "<>" would be a better choice than "*" but if rows with empty cells need to be counted then it will still be wrong.

 

[KRice]

I worked up a small table that used the SUM(SUMIFS construction with a term resembling rng,$B7:$B8, and after confirming as an array formula, I see your point...it works quite well. Thanks for the tip on this. I see one issue in the suggestion. To keep the names lengths shorter, it makes sense to keep "rng", but then in one place rng needs to be changed to rng3, as in:

=SUM(SUMIFS(INDEX(rng2,0,N$6),rng3,$B7:$B8,INDEX(rng,0,$BF$4),$BE$4,INDEX(rng,0,$BF$5),$BE$5,INDEX(rng,0,$BF$6),$BE$6,INDEX(rng,0,$BF$7),$BE$7,INDEX(rng,0,$BF$8),$BE$8,INDEX(rng1,0,14,{"<>Brk";"*"})))

where
rng =INDIRECT("'"&$BR$3&"'!"&"$A:$N")
rng2 =INDIRECT("'"&$BR$3&"'!"&"$W:$AH")
rng3 =INDIRECT("'"&$BR$3&"'!"&"$L:$L")

As for the issue with the minus sign in front of the results array involving the ="Brk" term, I like how you handled that by summing the array where the elements <>"Brk"...so hopefully there will be no surprises.

 

[jasonb75]

To keep the names lengths shorter, it makes sense to keep "rng", but then in one place rng needs to be changed to rng3, as in:

oops, those were typos on my part. I was intending to use rng1, rng2 and rng3 to keep it consistent with your earlier post but forgot to add the rng# numbers into it (most of the formula was spliced with copy and paste).

One final tweak as I notice that there is still an erroneous rng# in the formula, also changed the final criteria from * to <> based on my earlier observation.

=SUM(SUMIFS(INDEX(rng2,0,N$6),rng3,$B7:$B8,INDEX(rng,0,$BF$4),$BE$4,INDEX(rng,0,$BF$5),$BE$5,INDEX(rng,0,$BF$6),$BE$6,INDEX(rng,0,$BF$7),$BE$7,INDEX(rng,0,$BF$8),$BE$8,INDEX(rng,0,14,{"<>Brk";"<>"})))

With the final criteria, "<>" will pick up any cell that is not empty (including formula blanks), while "*" will ignore cells that contain numbers. As we don't know the data type, I think that including potential numeric data in the count might be a safer option, although if there are completely empty cells in the range INDEX(rng,0,14) then I don't think that this method will work. I haven't tested, but I think that adding an additional criteria to the array for empty cells could cause some rows to be counted twice.

Think I'll wait for feedback / clarification from @Grimm127 before trying to fix something that might not be broken.

 

[Grimm127]

Wow you two were great! Thank you for your suggestions. I am definitely going to create the rng1. But forgive my ignorance to the subject
But where do I create them?

Using sum product I am hesitant because I always heard those are huge memory hogs. Arrays on the other hand I have used before but limited.

I will use the {"<>Brk";"<>"}))) as you suggested as well.

Thank you in advance again. Wonderful stuff! I love excel I am always learning something new!

rng1 =INDIRECT("'"&$BR$3&"'!"&"$A:$N")
rng2 =INDIRECT("'"&$BR$3&"'!"&"$W:$AH")
rng3 =INDIRECT("'"&$BR$3&"'!"&"$L:$L")

So the result will be when I select the cell it will show something below.

=SUMIFS(INDEX(rng1,0,N$6),rng3,$B7,INDEX(rng1,0,$BF$4),$BE$4,INDEX(rng1,0,$BF$5),$BE$5,INDEX(rng1,0,$BF$6),$BE$6,INDEX(rng1,0,$BF$7),$BE$7,INDEX(rng1,0,$BF$8),$BE$8)-SUMIFS(INDEX(rng2,0,N$6),rng3,$B7,INDEX(rng1,0,$BF$4),$BE$4,INDEX(rng1,0,$BF$5),$BE$5,INDEX(rng1,0,$BF$6),$BE$6,INDEX(rng1,0,$BF$7),$BE$7,INDEX(rng1,0,$BF$8),$BE$8,INDEX(rng1,"Brk")+SUMIFS(rng2,0,N$6),rng3,$B8,INDEX(rng1,0,$BF$4),$BE$4,INDEX(rng1,0,$BF$5),$BE$5,INDEX(rng1,0,$BF$6),$BE$6,INDEX(rng1,0,$BF$7),$BE$7,INDEX(rng1,$BE$8)

 

[Grimm127]

Dang it. I just remembered. I can't use VBA for this. I am using a template which I have no problem with having modules and such but I have to send out a report as a non-macro. I am screwed. : (