+ Reply to Thread
Results 1 to 13 of 13

Countifs / Sumifs with dynamic arrays (spilled ranges)

  1. #1
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    477

    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?

  2. #2
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    477

    Re: Countifs / Sumifs with dynamic arrays (spilled ranges)

    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.

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    5,213

    Re: Countifs / Sumifs with dynamic arrays (spilled ranges)

    Those aren't the same formula at all. One is essentially an array of single cell COUNTIFS formulas, the other is an array of multi-cell COUNTIFS formulas.
    Rory
    I drink, and I know things

  4. #4
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    477

    Re: Countifs / Sumifs with dynamic arrays (spilled ranges)

    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.

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    5,213

    Re: Countifs / Sumifs with dynamic arrays (spilled ranges)

    As fun as it is after a long day at work to try and visualise that, a sample workbook might be useful...

  6. #6
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,138

    Re: Countifs / Sumifs with dynamic arrays (spilled ranges)

    Quote Originally Posted by esbencito View Post
    . . .
    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.

  7. #7
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    477

    Re: Countifs / Sumifs with dynamic arrays (spilled ranges)

    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?
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    477

    Re: Countifs / Sumifs with dynamic arrays (spilled ranges)

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

  9. #9
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,138

    Re: Countifs / Sumifs with dynamic arrays (spilled ranges)

    I just tested this in Web Excel, and it looks like this is something Excel won't handle. Here's a link to my workbook. With a spilled range of indices into another range in D1:D6, COUNTIF(INDEX(range,D1#),criterion) returns a 6-row spilled range of #VALUE! errors. OTOH, COUNTIF(INDEX(range,D1),criterion) works just fine in one cell and can be filled down into 5 more cells. Then again, COUNTIF(OFFSET(range,D1#-1,0,1,1),criterion) does work as a spilled formula.

    It appears the INDEX function with dynamic arrays as 2nd or subsequent arguments doesn't return an array of range references, but OFFSET does. Pity OFFSET is volatile.

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

    ADDED: I just tested this in Excel 2000 (which I run under wine on Linux), and with A1:B10 and D1:D6 as in the linked workbook on OneDrive, selecting F1:F6, the array formula =COUNTIF(INDEX(A$1:A$10,D$1:D$6),">"&1E4) works, that is, it produces the result ={0;0;1;1;1;1}. So either desktop Excel handles this differently than web Excel, or Excel has changed the semantics of INDEX in the newest versions which support dynamic arrays. However, since this doesn't work in web Excel, this may be something you should avoid.

    Further Added: In Excel 2000, the array formula =ISREF(INDEX(A1:A10,D1:D6)) produces the array result ={TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}. In web Excel, it produces the single result FALSE.

    Actually, weirder. I finally got around to loading a Windows VM to see how current desktop Excel handles this. The formula =COUNTIF(INDEX(A1:A10,D1#),">"&1E4) entered normally produces 6-row spilled result of all #VALUE! errors. However, if you select that range and re-enter this formula with [Ctrl]+[Shift]+[Enter] (traditional array formula entry), it produces ={0;0;1;1;1;1}. Web Excel doesn't allow array entry, but it does calculate array-entered formulas correctly. Anyway, it appears you need to coax Excel into behaving correctly by using traditional array formula entry for this, which somewhat reduces the utility of spilled formulas and dynamic arrays. That said, OFFSET works without traditional array formula entry, but it's volatile.
    Last edited by hrlngrv; 02-29-2020 at 06:18 PM.

  10. #10
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    477

    Re: Countifs / Sumifs with dynamic arrays (spilled ranges)

    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?

  11. #11
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,138

    Re: Countifs / Sumifs with dynamic arrays (spilled ranges)

    Quote Originally Posted by esbencito View Post
    . . . 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.

  12. #12
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    477

    Re: Countifs / Sumifs with dynamic arrays (spilled ranges)

    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...

  13. #13
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    5,213

    Re: Countifs / Sumifs with dynamic arrays (spilled ranges)

    I suspect you've just chosen a bad example for what you're trying to achieve, since I can't really see why you wouldn't simply enter:

    =COUNTIFS(Data!B:B,M5#,Data!C:C,N5#)

    in O5.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Dynamic, Spilled, Named Ranges - how to override SPILL
    By TimB in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-24-2020, 01:53 PM
  2. [SOLVED] YTD with dynamic spilled array
    By MatthewHart74 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 12-14-2019, 07:49 AM
  3. YTD with spilled dynamic array
    By MatthewHart74 in forum Office 365
    Replies: 0
    Last Post: 12-12-2019, 12:05 PM
  4. Performance of SUMIFS, COUNTIFs etc with large ranges
    By TheRobsterUK in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-15-2017, 06:19 AM
  5. SUMIFS/COUNTIFS for multiple conditions/ranges
    By BoudeyCall in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-31-2017, 04:35 PM
  6. SUMIFS/COUNTIFS for multiple conditions/ranges
    By BoudeyCall in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-09-2017, 09:11 PM
  7. [SOLVED] Rules for Multiple Criteria SUMIFS/COUNTIFS and Arrays
    By cmbh in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-12-2016, 08:22 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1