Countifs producing #value

[Marcushodgsonsydney]

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

[#2 · Accepted solution]

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

[#3]

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.

[#4]

@chahine 

Thanks - that worked