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]

I can't use VBA for this.

There is no vba involved, named ranges are formula based.

In excel go to the Formulas tab, then Name Manager (Alt + m, n).

Referring to the names and INDIRECT formulas provided by Kirk (@KRice) in post 7:-
Click 'New' then enter rng into the 'Name' box and the =INDIRECT(...) formula into the 'Refers to' box and click OK. Repeat these steps for rng2 and rng3.

Then use my formula from post 8 in your worksheet.

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.

Sumproduct is an array, it would be more efficient than some array formulas but less efficient than others. It's more a question of choosing the right functions for the task than believing what you've heard about one specific thing. You wouldn't use a small car to transport goods between warehouses and you wouldn't use a large goods vehicle for your weekly trip to the supermarket.

The SUM(SUMIFS(...)) formula is the most efficient one that you could use for what you need, as long as it gives the correct results, with the main issue being the content of column N. What is in the cells that don't contain 'Brk'? Do they all contain other data, are they blank (formulas that show "") or are they empty?

 

[KRice]

I also want to clear up some potential confusion: the formula is considerably shorter than in your summary @Grimm127...you show this:

=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)

But the actual formula to use is this from @jasonb75's last recommendation.

=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";"<>"})))

 

[Grimm127]

So I am creating the Named Range. But when I replace the

INDIRECT("'"&$BR$3&"'!"&"$A:$N") with rng1 I get a Value error....​

In the Refers to box it shows this
="INDIRECT(""'""&$BR3&""'!""&""$A:$N"") "

 

[jasonb75]

You have a lot of extra double quotes in there.

Copy the named range formulas from post 7, do not make any changes to them.

Then copy the single =SUM(SUMIFS(...)) formula from post 8 (the one that Kirk has re-posted at the bottom of post 12), again, do not make any changes to this formula.

What we've provided for you should do exactly what you need without any alterations.

 

[KRice]

OK...there might be an issue getting the Name Manager to recognize a range embedded within INDIRECT. As an alternative, this should work:
Have a look at cells F1:F3 below. Assign the range names shown in E1:E3 to the content in F1:F3, respectively, using the Name Manager. Note that the formulas in F1:F3 refer to cell $BR$3 where I have type Sheet 27...so the concatenated text string in, say F1, is constructing the appropriate range reference...so don't type the formula showing "Sheet 27"...use the version in the cell formulas table, e.g.,
"'"&$BR$3&"'!"&"$A:$N"

mrexcel_20200416.xlsm
	E	F	G
1	rng	'Sheet 27'!$A:$N	3
2	rngb	'Sheet 27'!$W:$AH	1
3	rngc	'Sheet 27'!$L:$L	14
Sheet15

Cell Formulas
Range	Formula
F1	F1	="'"&$BR$3&"'!"&"$A:$N"
G1	G1	=INDEX(INDIRECT(rng),4,1)
F2	F2	="'"&$BR$3&"'!"&"$W:$AH"
G2	G2	=INDEX(INDIRECT(rngb),4,1)
F3	F3	= "'"&$BR$3&"'!"&"$L:$L"
G3	G3	=INDEX(INDIRECT(rngc),4,1)

In this example, I have some random data on a sheet named "Sheet 27", and the INDEX(INDIRECT(range)) formulas in G1:G3 are pulling information from that sheet.
Then use @jasonb75 's suggestion, except wrap every reference to rng, rng2, or rng3 in an INDIRECT, as in:

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

I've edited the formula at the end...I thing there was a misplaced right parenthesis...one was need to close the last INDEX function.

 

[jasonb75]

there might be an issue getting the Name Manager to recognize a range embedded within INDIRECT.

Have you encountered problems with doing it that way, Kirk? It works fine for me the way that we had already suggested.

 

[KRice]

Oh...I forgot to mention something. I think the range names may need to be changed...Excel doesn't seem to like range names that end in a number, so add another non-digit character or use a, b, c.

About your question, Jason, yes, I have encountered some issues, but I can get it to work the original way too. I believe the problem concerns Name Manager. Aside from the range name issue just mentioned, I've found that it's easier and more reliable to first copy (to the clipboard) the text string that will be embedded in the INDIRECT function. Taking "rng" as an example, I go to Name Manger, select New, then type rng in the "Name" field, and then go to the "Refers to" field and delete entirely anything in that field, and then type =INDIRECT( then paste from the clipboard and then type )

What is interesting is that the right parenthesis doesn't show up...you have to trust that it entered correctly...and then confirm the entry with OK. Any attempt to navigate within the "Refers to" field introduces other content that is not wanted. So it is somewhat tedious, but it appears to work if care is taken making the original named range assignments. Other things I've tried, such as pasting the text inside INDIRECT() have not worked...for some reason, pasting the function doesn't work, but typing the function name does.

 

[jasonb75]

You can navigate the refers to box by holding left click and dragging left / right, but not by using the cursor keys. This works fine as long as the formula in the box is only a short one, but longer formulas become difficult to navigate.

Easiest way is to enter the formula in a worksheet function to validate it, i.e. =COUNTA(INDIRECT(....)), once working, remove the wrap function and copy / paste the rest to the name manager.

The name references that we have posted in the forum are all valid, so copying them directly into the name manager should not be an issue.

 

[Eric W]

Oh...I forgot to mention something. I think the range names may need to be changed...Excel doesn't seem to like range names that end in a number, so add another non-digit character or use a, b, c.

You can't use a Name for a range that can be confused for a cell reference. In Excel 2007 and newer, RNG1 is a valid cell name (and RNG2, RNG3, RNG4, etc.).

 

[KRice]

Ahhh!...Jason and Eric, thank you both! You've each managed to resolve some of my pet peeves with Excel. I used the range assignment tip and it worked great. I suspect the Name Manager behavior is a source of frustration for many users.

So the preferred option for the OP is to try again with Name Manager, assigning range names that don't violate the naming convention...maybe rng, rngb, and rngc, and then use the post 8 formula, except make sure that the end of that formula reads as 14),{"<>Brk";"<>"}))
A right parenthesis was moved from the end of the formula to close off the last INDEX function (immediately after the "14").