Forum Discussion
COUNTIFS to return values in a table
Thank you everyone for your help!! I was completely overcomplicating and really just needed a simple IF= statement. But this was a great learning experience!!
COUNTA(FilteredBudgets) returns 1 if FILTER returns #CALC! error. It counts all not empty cells, doesn't matter they have values or errors. Thus IFERROR never returns zero.
Better ROWS instead of COUNTA.
Thank you!
I ended up using COUNT instead of COUNTA. It resolved my problem...I will try ROWS as well to test in case of future need. I do want to be sure that COUNT is correct not just here, but in other instances where I am counting data to output. Do you have thoughts on whether that would work across other scenarios similar to this one?
COUNT counts numbers only. Thus ignores texts, Booleans and errors. If you'd like to ignore errors only that could be
=SUM(--NOT( ISERROR( array ) ) )What to use depends on what exactly you'd like to count. If vertical or horizontal size of the array, when ROWS and COLUMNS work. If number of elements in 2D array ignoring errors when as above. Etc.
As everything in Excel practically any calculation could be done by several ways. For example, in addition to COUNT or ROWS for the sample, we may use
IF( ISERROR( @FilteredBudgets ), 0, COUNTA( FilteredBudgets ) ), perhaps something else. All depends on situation.