One cell in an array forumla not returning expected values

Maybe a space in the empty cell?

Is it finished by Ctrl-Shift-Enter? Try to CSE or copy the correct row and paste again.

space in an empty cell, or leading/training spaces in text?

I went through and looked for trailing / leading spaces as well as rogue spaces in the table data but I still wasn't able to correct the problem. I even changed my naming scheme and went through and re-entered the numerical values to try to ensure data integrity, but the problem is still there. I wonder if someone would be willing to look at my spreadsheet for me and see if they can tell what the problem might be?

The issue is occurring on the "Average Data" worksheet. The number 2 should be in cell D31 because there are 2 data instances on tab jan-march for the last row.

Thank for any suggestions.

SpreadSheet.zip

2 things....

1. you formula in Jan-Mar stops at row 195

2. here is a simpler formula than the array you are using....
=IF(SUMIFS('Jan-March'!B:B,'Jan-March'!E:E,$B5,'Jan-March'!B:B,"<>")=0,"",SUMIFS('Jan-March'!B:B,'Jan-March'!E:E,$B5,'Jan-March'!B:B,"<>"))

Try it like this...

=IFERROR(1/(1/SUMIF('Jan-March'!E:E,$B5,'Jan-March'!B:B)),"")

In Excel we can say that 1/(1/some number) will always equal some number *except* for when some number = 0. In that case 1/(1/0) will return a #DIV/0! error.

This is a shorthand method of error trapping. If the result of the SUMIF is 0 you wanted to return a blank instead.

So, instead of using this for the 0 test:

=IF(SUMIFS('Jan-March'!B:B,'Jan-March'!E:E,$B5,'Jan-March'!B:B,"<>")=0,"",

We can replace that with the shorter and more efficient:

=IFERROR(1/(1/.....),"")

If the SUMIF results in 0 then 1/(1/0) returns #DIV/0! and the IFERROR traps the error and returns a blank instead.