SOLVED

New Contributor

# 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 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 Disc Disc L1 Disc L2 Total No Disc Disc L1 Disc L2 Total 3 5 8 3 5 8 3 5 8 3 5 8 2 4 6 2 4 6 2 5 7 2 5 7 2 5 7 2 5 7 2 5 7 2 5 7 3 6 9 3 6 9 2 1 7 10 2 1 7 10 3 5 8 3 5 8 2 4 6 2 4 6 2 4 6 2 4 6 6 6 6 6 2 4 6 2 4 6 2 4 6 2 4 6 2 4 6 2 4 6 2 4 6 2 4 6 5 5 5 5 3 5 8 3 5 8 5 1 12 18 5 1 12 18 3 5 8 3 5 8 3 5 8 3 5 8 2 1 7 10 2 1 7 10 2 1 7 10 2 1 7 10 12 12 12 12 3 5 8 3 5 8 3 5 8 3 5 8 3 5 8 3 5 8 3 5 8 3 5 8 3 5 8 3 5 8 2 4 6 2 4 6 2 4 6 2 4 6 2 4 6 2 4 6 96 4 149 249 96 4 149 249 2 4 6 5 5 7 4 11 103 4 153 260 96 4 149 249 #VALUE! 32
3 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Countifs producing #value

COUNTIFS(D3:D51,">0")+countifs( D54:D55,">0")
you should use + in between as the ranges are of different size, hope this helps

# Re: Countifs producing #value

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.

# Re: Countifs producing #value

Thanks - that worked