Forum Discussion
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 |
COUNTIFS(D3:D51,">0")+countifs( D54:D55,">0")
you should use + in between as the ranges are of different size, hope this helps
3 Replies
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.
COUNTIFS(D3:D51,">0")+countifs( D54:D55,">0")
you should use + in between as the ranges are of different size, hope this helps
