Excel array formula not behaving as expected

Question

I have a little table in EXCEL that uses COUNTIFS to count the number of instances in a dataset that matches four criteria that the user can select from a data validated list in the first four cells across the table. The COUNTIFS live in the fifth column and spits out the number. This is working no problem.

I now want to add "ANY" to each of the 4 validated lists so that the user can effectively "ignore" one or more of the criteria by selecting the ANY option. So the extreme example would be ANY in each of the four cells, which would return the entire number of rows in the dataset, counting every entry (not that that's useful but just to illustrate what I'm after!)

I am using some helper cells (L45:T48) and using the following array formula to try and achieve this

{=SUM(COUNTIFS(range1, L45:T45, range2, L46:T46, range3, L47:T47, range4, L48:T48))}

the helper cells simply contain a single IF statement:

=IF(C57<>"ANY",C57,{"Scheduled","Discretionary"})

such that if the user chooses a specific criteria, the cell just mirrors that criteria, but if the user selects "ANY" then the array of all the criteria in put into the helper cell range for the array formula to cycle through.

Not surprisingly, as I'm posting here, it's not quite working!

It's still working if specific criteria are selected, but if ANY is selected, then it seems to be just selecting the first element in the array and will give the same return value as if that criteria is selected specifically.

I'm hoping I've missed something obvious that jumps out at someone as to why the formula doesn't seem to be cycling through the array elements as expected. There is a tiny delay when I select a criteria before the return value appears, so it seems as if it is going through each cell in the helper cell ranges, just not picking up the data?

Thanks so much for your reply Owen. Sorry, I am using Excel 365 which supports dynamic arrays, BUT the excel file is for distribution to a group, some of which have earlier versions. Also apologies if editing the OP isn't the best way to "reply". (New user here obviously!)

I thought I'd try anyway, to see if at least using the dynamic array solution worked to start with, and I culled the problematic formula just down to the first two criteria ranges. Weirdly, your suggestion gets me one step further but not all the way. So with just two criteria now to deal with, using # instead of a range (eg L45:T45), allows one "ANY" to work as intended. However, when both are selected as ANY it snaps back to just selecting the first criteria in each range. There are currently 73 rows. With criteria one (user name) selected as ANY I get 45 when I select Scheduled, and 28 when I select discretionary. Both correct. However, when I select ANY for the 2nd criteria also, instead of 73, I get 8 - which is the number of scheduled entries made by the first user listed in the first criteria array.

Your solution used SUMIF inside the SUM function whereas I need COUNTIF. I assume the syntax should translate to both but is that assumption valid?

Perhaps there is a way to use the AND logic statement to stitch the four COUNTIFs together since they don't seem to play nice inside the same brackets?! I couldn't make that work either though.

Answer 1

If you have the latest version of Excel which supports dynamic arrays, this will work:

=SUM(SUMIF(F3:F7,C3#,G3:G7))

The important part here is C3#, which will collect all of the items returned by the formula in cell C3 (which returns two items if someone selects ANY in cell I3).

This is the formula that returns the selected array (for visibility):

=IF(I3<>"ANY",I3,{"Scheduled","Discretionary"})

You can of course nest the formula in cell C3 inside the formula in J3:

Either way, this approach should help you get where you need to.

EDIT:

To extend to multiple selection criteria, you could add a column to your data table which returns TRUE if the selection criteria are met, or FALSE otherwise. Then you simply count the rows where that column is TRUE. If you prefer, you could return some meaningful text instead (like "Selected" and "Not selected").

To demonstrate that this works with 3 criteria, I created a column with this formula:

=AND(OR(E3=$J$3,$J$3="ANY"),OR(F3=$K$3,$K$3="ANY"),OR(G3=$L$3,$L$3="ANY"))

It's easy to see that for each selection criteria, you just add a new OR statement to the AND.

Then, the count of the selected rows is just:

=COUNTIF(H3:H7,TRUE)