Forum Discussion
Empty Array #CALC! error where arrays are not empty
I am running this FILTER formula to sum distinct values in 'June' column D based on criteria in 'June' columns Q and C. I get only an "empty array" error.
=SUM(--(LEN(UNIQUE(FILTER('June'!D:D,('June'!Q:Q>=$D$18)*('June'!C:C="110"))))>0))
I may be thinking about this too simply but none of the D, Q, or C arrays are empty. Used (INT(COUNT('June'!D:D))) to verify arrays. What am I missing?
JeremyD Don't know what's missing, but I created a mock-up of what I believe your worksheet looks like, I do not get the #CALC error when I remove the quote marks around the 110 in the second filter criteria, assuming that column C contains numbers. Not texts that look like numbers.
But it seems that the following formula should come up with the same result.
=COUNTA(UNIQUE(FILTER(E:E,(June!Q:Q>=$D$18)*(June!C:C=110))))
Up to you to determine if this is what you want. If it is not, perhaps you can upload a schedule that demonstrates your real situation and indicate the results you would want to see.
4 Replies
JeremyD Don't know what's missing, but I created a mock-up of what I believe your worksheet looks like, I do not get the #CALC error when I remove the quote marks around the 110 in the second filter criteria, assuming that column C contains numbers. Not texts that look like numbers.
But it seems that the following formula should come up with the same result.
=COUNTA(UNIQUE(FILTER(E:E,(June!Q:Q>=$D$18)*(June!C:C=110))))
Up to you to determine if this is what you want. If it is not, perhaps you can upload a schedule that demonstrates your real situation and indicate the results you would want to see.
Riny_van_Eekelen Thank you! For the record I used the COUNTA function as it reads simpler but removing the quotation marks from the original formula also returns the desired result.
I am very grateful!
As variant, that could be
=ROWS(UNIQUE(FILTER(June!D:D,(June!Q:Q>=$D$18)*(June!C:C=110)*(June!D:D<>""))))
