Countifs and Filtered Array

Question

Hello Community -

I am trying to find the smallest value within a matrix greater than 0. I am using the filter function which returns the array (12 x 5 matrix) [I have named this function CountryFilter] I then try to use the countifs to count the number of 0's and add the count, but continue to get #VALUE . Here is currently where I am at with no luck.

SUM(SMALL(CountryFilter,Countif(CountryFilter,0)))

Thanks

Mike

Answer 1

I'd have to see a copy of the workbook (without sensitive data).

Answer 2

Lastly, and apologies for all the posts. I just replaced the named range for the array for the SMALL function and it worked when the spilled array was still referenced in the countif function. but when i replaced the array in the countif function with the named range the error returned.

-mike

Answer 3

Also (not sure if it helps), but I have a spilled data at the top just to make sure the filter function is working properly. When I reference that spilled array inside the formula it works but not when I use the formula as a named range.

-mike

Answer 4

Thanks for the quick reply HansV - I have 365 so I do not need the CSF. However I tired both the Small and MINIFS as suggested and get a spilled array filled with #Value errors.

Thanks

Mike

Answer 5

There is no point in using SUM here.

I would expect SMALL(CountryFilter,Countif(CountryFilter,0)) to return 0.

You could try

=SMALL(CountryFilter,COUNTIF(CountryFilter,0)+1)

or

=MINIFS(CountryFilter,CountryFilter,">0")

or if you have an older version of Excel, the following array formula confirmed with Ctrl+Shift+Enter:

=MIN(IF(CountryFilter>0,CountryFilter))