Forum Discussion
Solved
Countifs producing #value
I am trying to expand a countif formula =COUNTIF(d3:d51,">0") to countifs to include a second range =COUNTIFS(D3:D51,">0", D54:D55,">0") but get the #value failure. Error checking suggests its some...
COUNTIFS(D3:D51,">0")+countifs( D54:D55,">0")
you should use + in between as the ranges are of different size, hope this helps
I think you could also use:
=INDEX(FREQUENCY((D3:D51, D54:D55),0),2)
It appears you are trying to count the values in column D, but exclude the subtotals? In that case, you might consider using the subtotal function (subtotal function ignores other subtotals and can sum, option 9, or count, option 2).
D52 =SUBTOTAL(9,D3:D51)
D56 =SUBTOTAL(9,D54:D55)
D58 =SUBTOTAL(9,D3:D57)
D60 (count) =SUBTOTAL(2,D3:D58)
Edit: the subtotal method will work as long as the cells are empty, but not if they actually contain a "0" number value.