Building a summary utlizing Indirect, Index and Match in a single reference

[LO_Padawan]

Good morn’n,

Working on a spreadsheet that will summarize repeating items within a list of items on a separate tab which has been accomplished with:

=IF(K2=0,0,IFERROR(INDEX($‘Widgets’.$AL$3:$‘Widgets’.$AL$4000, MATCH(0, COUNTIF($K$1:K2,$‘Widgets’.$AL$3:$‘Widgets’.$AL$4000) + (COUNTIF($‘Widgets’.$AL$3:$‘Widgets’.$AL$4000, $‘Widgets’.$AL$3:$‘Widgets’.$AL$4000)<1), 0)), 0)).

Looking at 4000 rows of data bogs the computer down with each entry. The objective is to utilize INDIRECT and make reference to $AL$4000 as a variable that is adjusted by a cell that is keeping track of the number of rows being used on the Widgets tab.

Thanks in advance for your time.

[keme1]

Can’t wrap my mind around the workings of your dataset/formula, but summarizing is often easier to accomplish by way of a pivot table. Have you looked into that?

One potential disadvantage is that pivot tables are not “live”; you have to manually request an update.

[Lupp]

What do you expect the following part of your formula to do?

+ (COUNTIF($Widgets.$AL$3:$Widgets.$AL$4000; $Widgets.$AL$3:$Widgets.$AL$4000)<1)

(I removed the superfluous apostrophes and replaced the comma as the parameter separator by the globally recognized semicolon.)

Having assigned a name, say WdatAL to the range $Widgets.$AL$3:$Widgets.$AL$4000 , the expression becomes
+ (COUNTIF(WdatAL; WdatAL)<1) .
Strange! As I see it, it will return an equivalent to ISBLANK(WdatAL) due to subtle details cvoncerning COUNTIF(). However, its hard to believe that was the idea.

Also:
Will your formula be entered the standard way or for array-evaluation?
Formulas with many subexpressions must be developed in steps making sure that every part works as expected. Mostly it’s clever then to keep the used helper columns, and this way to be prepared to make corrections, enhancements, upscale …
Subexpressions returning arrays may be treated differently.
Anyway: You will know the structure of your data and your intentions. Somebody only seeing a complicated formula can’t give detailed advice.
If you need effcient help you will have to provide your sheets (or reduced versions) and to explain in clear language of everyday what you want to achieve.

[LO_Padawan]

Formula in Action.ods (21.9 KB)
My apologies for leaving out an example.

In the attached example spreadsheet, instead of having a fixed range from row 2 to row 4004 in the formulas found in column “A” of the summary tab, have the end of the range to equal the value in D2 on the Summary tab. The Widgets tab contains a manually entered list of items and associated quantities to be summarized on the other tab.

I trust this helps.

[Lupp]

This isn’t an answer to the original question, but a solution for the task.
The formulas used by the OQer are a bit strange and complicated. With thoroughly reworked formulas the goal can be reached, and the time-efficiency can be enhanced by a low factor.

Actually what the OQer wants to achieve is exactly what the Pivot-Table-engine (formerly DataPilot) is made for - als already @keme1 supposed.

The creation of a pivot table is simple and well described in the Calc guide.
I therefore only attach an example with a ready-made PT and an image of the dialog usable for it.
The extra is a UDF organizing automatic updates. Try it:
disask87401dataPilotDemo.ods (106.2 KB)