Countifs / Sumifs with dynamic arrays (spilled ranges)

Hi all,

Excel released 7 new formulas and the capability to "spill" ranges, which I have been using frequently and mostly successful. However, I do run into issues sometimes and are confused whether I am doing it wrong or if some of the formulas are not working properly when referencing dynamic arrays.

e.g.

I can use:

Please Login or Register  to view this content.

where "$AC$12#" refers to the array with the row numbers in my data set

but I cannot use:

Please Login or Register  to view this content.

which for me is the exact same thing, but throws an "#VALUE!" error??

I am aware that INDEX:INDEX returns a range, but shouldn't INDEX('Data'!$1:$1048576,$AC$12#) also return the range?

btw,

Please Login or Register  to view this content.

works perfectly fine when leaving out the COUNTIFS formula. The range is spilling as you would expect.

Now that you say it, that's starting to make sense...

So what about below formula, maybe my understanding about array formulas in general is still limited, hence hoping to shed some light.

Please Login or Register  to view this content.

AC12#,AD12#,AE12# and AF12# are the dynamic arrays with the row numbers from the data sheet. First cell is supposed to count "CLASSIC" in the range coming from the data sheet with AC12# row numbers. The second cell should then count "CONCEPT", but use row numbers from array AD12# instead, the third cell should use AE12# and so on. Why isn't that working?

Please Login or Register  to view this content.

I am getting the correct results if I just reference one array e.g. AC12# as above. Really confused here.

Originally Posted by esbencito

. . .

Please Login or Register  to view this content.

. . .

INDEX($AC$12#,1) is a reference to $AC$12, so might as well just use $AC$12 without the INDEX call.

INDEX($AC$12#,ROWS($AC$12#)) refers to the last cell spilled result, ASSUMING that range spills vertically. Safer to use INDEX($AC$12#,ROWS($AC$12#),COLUMNS($AC$12#)).

Is AC12# a range of sequential positive integers?

. . . but I cannot use:

Please Login or Register  to view this content.

which for me is the exact same thing, . . . but shouldn't INDEX('Data'!$1:$1048576,$AC$12#) also return the range?

If AC12# ultimately produced the values {3;13}, INDEX(range,{3;13},c) would return references to INDEX(range,3,c) and INDEX(range,13,c), which is NOT the same as INDEX(range,3,c):INDEX(range,13,c). Thus my question whether AC12 returns sequential positive integers. Even then, you'd have an array of references to single cells rather than a single reference to multiple cells. That is, something more like (INDEX(range,AC12,c),INDEX(range,AC13,c),INDEX(range,AC14,c),...) than INDEX(range,AC12,c):INDEX(range,AC#,c).

That said, if your 2nd formula is producing an error rather than a spilled result, maybe INDEX(range,dynamic_array,...) produces a result COUNTIFS can't handle. Since MSFT has NEVER formally documented expressions which APPEAR TO BE/BEHAVE LIKE arrays of range references, it's a matter of trial and error to determine what works and what doesn't. This may be one of the things which doesn't work.

Thanks rorya and hrlngrv for the feedback so far! I do realise now that I was referencing an array of single cells rather than multiple cells. Unfortunately, that doesn't help me solve the issue I am facing when referencing multiple dynamic arrays as illustrated in #4, hence I have mocked up a sample workbook.

Please be aware though that I have overly simplified what I am trying to achieve, I do know that this could be done with e.g. a pivot table, but I am interested in learning more about the new dynamic arrays. I have included the desired results on the output sheet in cell "Q5" as well as the formula, which I have been trying to use, in cell "O5".

Any ideas what I am doing wrong?

So is this one of these things that are not achievable?

Thanks for the very in-depth analysis and explanation. Very much appreciated that you took the time to look into this. I was indeed able to solve it with OFFSET a few days ago, that's why I got confused when it didn't work with INDEX. Unfortunately as you already mentioned, it is a volatile function and I pretty much always avoid it for that reason. It's been causing so much trouble in the past (performance) that I sometimes even forget it exists.

Interesting also that there are differences between web and desktop versions and CTRL+SHIFT+ENTER and ENTER. Possibly a bug and fixed in future updates? Hard to imagine that this was intentional, however, it is MSFT after all, so who knows...

That said, with the new FILTER function, which MSFT has, er, borrowed from Google Sheets, all the work it has expended on SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGIFS, etc has been rendered pointless. The actual formula which worked, =COUNTIF(OFFSET(A1:A10,D1#-1,0,1,1),">"&1E4) could be handled by =--(FILTER(A1:A10,B1:B10<>"x")>1E4)

Not sure I am 100% following here. So you are saying the new FILTER function can do everything that SUMIFS / COUNTIFS used to do and possibly more? If that is the case, how would I then use FILTER instead of below formula to get the required results that I mocked up in my sample workbook?

Please Login or Register  to view this content.

In brief, FILTER eliminates the need for all the *IF[S] and *LOOKUP functions, and probably also all the (stultified) D* (e.g., DSUM) functions. To be clear, XLOOKUP would still be more efficient for finding a SINGLE value corresponding to its 1st argument as long as that single value would be the 1st or last corresponding value. Once you need 2nd or 3rd, etc, INDEX(FILTER(...),...) becomes the new go-to idiom.

Agree that XLOOKUP will be more efficient for finding single values. Is the FILTER function generally faster than SUMIFS though? Or is SUMIFS superior in terms of speed/ performance?

Originally Posted by esbencito

. . . Interesting also that there are differences between web and desktop versions and CTRL+SHIFT+ENTER and ENTER. Possibly a bug and fixed in future updates? Hard to imagine that this was intentional, . . .

It's intentional to the extent Web Excel doesn't recognize [Ctrl]+[Shift]+[Enter]. MSFT may want to say its web version can't reliably obtain that key combination from every browser, but FWIW LibreOffice Calc running remotely through rollApp accepts [Ctrl]+[Shift]+[Enter] for array formula entry, and IIRC so did EditGrid while it was around. FWIW, Zoho's spreadsheet accepts [Ctrl]+[Shift]+[Enter]. So it looks intention that Web Excel doesn't accept traditional array formula entry. However, Web Excel does calculate traditionally entered array formulas correctly in workbooks created using desktop or mobile(!) Excel. [Limited as Mobile Excel is, it handles traditional array formula entry. Why? Because MSFT.]

Historical digression: Excel 2003 and prior could handle only 7 levels of nested function calls. Actually, that was a limitation of Excel's formula parser, not Excel's calculation engine. I still have a Lotus 1-2-3 .WK4 workbook with the formula

@SUM(@SUM(@SUM(@SUM(@SUM(@SUM(@SUM(@SUM(@SUM(@SUM(@SUM(@SUM(@SUM(@SUM(@SUM(@SUM(@SUM(@SUM(@SUM(A1,A2),A3),A4),A5),A6),A7),A8),A9),A10),A11),A12),A13),A14),A15),A16),A17),A18),A19),A20)

which Excel 2003 happily converted to

=SUM(SUM(SUM(SUM(SUM(SUM(SUM(SUM(SUM(SUM(SUM(SUM(SUM(SUM(SUM(SUM(SUM(SUM(SUM(A1,A2),A3),A4),A5),A6),A7),A8),A9),A10),A11),A12),A13),A14),A15),A16),A17),A18),A19),A20)

and correctly calculates as values in A1:A20 change. Excel 2003 just couldn't handle editing it. Regardless, it showed that MSFT has never been fully forthcoming about Excel's actual functionality and the precise reasons for its limitations.

. . . you are saying the new FILTER function can do everything that SUMIFS / COUNTIFS used to do and possibly more? . . .

For the most part. FILTER only works by row, and there could be mixed 1D by row and by column constructs which it can't handle easily.

. . . how would I then use FILTER instead of below formula [not shown] to get the required results that I mocked up in my sample workbook? . . .

If you mean your attached workbook in #7, and you want the results in Output!M5:O16, then enter the formula

M5: =UNIQUE(Data!$B$5:$C$113)

which will spill into M5:N16. Then

O5: =MMULT((TRANSPOSE(Data!$B$5:$B$113)=INDEX(M21#,0,1))*(TRANSPOSE(Data!$C$5:$C$113)=INDEX(M21#,0,2)),SEQUENCE(ROWS(Data!$D$5:$D$113),1,1,0))

or

O5: =TRANSPOSE(MMULT(SEQUENCE(1,ROWS(Data!$D$5:$D$113),1,0),(Data!$B$5:$B$113=TRANSPOSE(INDEX(M21#,0,1)))*(Data!$C$5:$C$113=TRANSPOSE(INDEX(M21#,0,2)))))

both of which will spill into O5:O16. The latter, though longer and needing a 3rd TRANSPOSE call, may still be more efficient since it's transposing 12x1 and 1x12 ranges rather than 109x1 ranges.

Duplicating the functionality of pivot tables is actually something FILTER doesn't do well. You'd need to use

O5: =ROWS(FILTER(Data!$B$5:$C$113,(Data!$B$5:$B$113=M5)*(Data!$C$5:$C$113=N5)))

but that doesn't spill, so you'd need to fill it down into O6:O16. Also, spilled formulas don't seem to work in tables.

As for recalc speed, I haven't checked.

If you mean your attached workbook in #7, and you want the results in Output!M5:O16, then enter the formula

M5: =UNIQUE(Data!$B$5:$C$113)

which will spill into M5:N16. Then

O5: =MMULT((TRANSPOSE(Data!$B$5:$B$113)=INDEX(M21#,0,1))*(TRANSPOSE(Data!$C$5:$C$113)=INDEX(M21#,0,2)),SEQUENCE(ROWS(Data!$D$5:$D$113),1,1,0))

or

O5: =TRANSPOSE(MMULT(SEQUENCE(1,ROWS(Data!$D$5:$D$113),1,0),(Data!$B$5:$B$113=TRANSPOSE(INDEX(M21#,0,1)))*(Data!$C$5:$C$113=TRANSPOSE(INDEX(M21#,0,2)))))

Thanks! I get the correct results, however, since I have oversimplified what I am trying to achieve (my bad!), referencing the whole range "Data!$B$5:$B$113" won't work in my data set. Hence we will be facing the same issue again with using an additional INDEX function where dynamic arrays as a 2nd or subsequent argument won't work as they don't return an array of range references. I am using OFFSET for now until I find a better solution.

Duplicating the functionality of pivot tables is actually something FILTER doesn't do well. You'd need to use

O5: =ROWS(FILTER(Data!$B$5:$C$113,(Data!$B$5:$B$113=M5)*(Data!$C$5:$C$113=N5)))

but that doesn't spill, so you'd need to fill it down into O6:O16. Also, spilled formulas don't seem to work in tables.

Exactly my problem with using the FILTER function. It works great for other things, but it won't spill like SUMIFS does in this case. I am building dashboards, hence spilling and auto resizing is essential as I don't know how much data will be found in the data set beforehand...