arrays, OLE action errors, IndexMatch and Calendars

[phpolicylady]

Excel Geniuses, I'm in need of some inspiration here.

I am attaching a file that has 3 tabs currently.

I have '2019query' which isn't live in this file. in addition to the query table, I have added several columns to help create a calendar of summer events at different campus locations.
The locations tab has the table of locations plus a countifs for the total number of programs that a campus has. Next to that table I have a series of columns with indexmatch used to pull the specific campuses that have 1 program, 2 programs, etc.
The tab called '1' is my actual trouble right now.

You will see a drop down menu using validation for the campuses with only 1 program, an array pulling that data from the query, and finally a calendar.

So I'm having trouble with the array and the formulas below the array.

My array is supposed to pull in the matching records based on the drop down selection. Specifically, it is supposed to pull the data in columns BX:EB.
Under the array, I have a concatenate formula in row 18. It is supposed to check to see if there is anything in the first row for that day and add it with a CHAR(10) to begin a new row for the next item - or skip it all the way through the array.
Row 19 is supposed to sum the total number of students expected for that day based on if there is a program listed for that day.

Finally, my calendar is just a series of basic if statements.

In my real file, with the working query, the array worked just fine, so I'm not sure why that isn't working in the clean version I uploaded. but the sum in row 19 is not seeing an empty array cell value as really being empty. so it sums the students even when there isn't a program.

BTW - if you have a sweeter, better, cooler way to create this whole thing from my query...I'm ALL EARS!!!! This is a series of headaches to make something that I wich could just be easy.

[Melvosh]

The reason you're not seeing the data you expect is because there are #N/A values in your data table. Try this array formula, entered in F5 with ctrl+shift+enter:

=IFERROR(INDEX('2019query'!BX$2:BX$600,SMALL(IF(IFERROR('2019query'!$CA$2:$CA$600=$A$1,FALSE),ROW('2019query'!$CC$2:$CC$600)-ROW($J$2)+1,""),ROWS($1:1))),"")

The red portion turns the data table errors into FALSE, which then allows your formulas to work. Does that return the results you're expecting?

[phpolicylady]

So the array works just fine in my original file, just not the one I uploaded (no idea why that would affect anything...)

But my main problem now is the formulas in row 19 under my array. I'm trying to have it do a sumifs for any program happening that day, but currently it just adds every program.

I think the problem is that I'm technically finding something (not null) in each cell of my array, so it is adding the students for all of the programs even if you can't see them on that day. Basically, the array is giving the sumifs something to say, "yep, there's a program that day" even though it's just the empty cell returned by the array.

Did that make any sense?

Here's my formula for row 19:
=SUMIFS($BK$5:$BK$16,M5:M16,"<>")

I'm certain that the trick lies in the "<>" part of the formula...I just can't find a way to make excel see an empty return value in an array as really a null value. I tried 0 and "<>0" but no luck.

[Melvosh]

The problem appears to be column I on sheet '1'. It has a #REF in your uploaded file. If you skip that column with the array formula, and make sure this formula is in J5 and drag over and down, you should see the results you want:

=IFERROR(INDEX('2019query'!CA$2:CA$600,SMALL(IF(IFERROR('2019query'!$CA$2:$CA$600=$A$1,FALSE),ROW('2019query'!$CC$2:$CC$600)-ROW($J$2)+1,""),ROWS($1:1))),"")

Does that solve the issue you're seeing?

[phpolicylady]

Melvosh - thanks, but that's the part that is working in my actual file. It is only not working the in the uploaded one.

I'm in a quandary with the sumifs in row 19 now.

[Melvosh]

Just to make sure I understand, if you select '004-JYJY' in cell $A$1 on sheet '1', are you expecting 0 in column M, and 30 in column R on row 19?

[phpolicylady]

yes - that is exactly what I want to happen

[Melvosh]

Try this in M19 and drag over:

=SUMPRODUCT(--(M5:M16<>""),$BK$5:$BK$16)

SUMPRODUCT adds overhead, but it will do what you want. There may be a tweak to SUMIF to do what you're looking for, but I'm not sure what it might be. SUMIF is counting the cells with formulas, because technically they are not blank or empty.

[phpolicylady]

YES!!!! That works!!!

I am very grateful!!!!

[Melvosh]

Happy to help!