SOLVED

Countifs producing #value

New Contributor

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 something to do with data type but I believe I am only using numbers and everything I try gives the same result.  Any suggestions on how to fix this?  Couldn't find a way to attach a doc so the pasted table shows the failed version on the left and the original version which worked on the right.

 

Budget  Budget
No DiscDisc L1Disc L2Total  No DiscDisc L1Disc L2Total
3 58  3 58
3 58  3 58
          
2 46  2 46
2 57  2 57
2 57  2 57
          
          
2 57  2 57
          
3 69  3 69
21710  21710
3 58  3 58
2 46  2 46
          
2 46  2 46
6  6  6  6
2 46  2 46
2 46  2 46
2 46  2 46
2 46  2 46
5  5  5  5
3 58  3 58
          
511218  511218
3 58  3 58
          
          
3 58  3 58
21710  21710
21710  21710
12  12  12  12
3 58  3 58
3 58  3 58
          
3 58  3 58
3 58  3 58
          
          
3 58  3 58
          
2 46  2 46
2 46  2 46
          
          
          
2 46  2 46
          
          
964149249  964149249
          
2 46      
5  5      
7 411      
          
1034153260  964149249
   #VALUE!     32
3 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

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.

@chahine 

 

Thanks - that worked