COUNTIFS issue

[beedistinct]

I'm trying to take an average of columns of numbers corresponding to another date column if that has a valid date or blanks.
I figured AVERAGEIFS wouldn't work here because there is no way I could provide an OR condition. So I decided to use SUMIFS and COUNTIFS instead.
WHat I'm doing is first summing the numbers with valid dates and counting them and then summing the numbers for which I have a blank date and counting them. Then I'm adding the sums and the counts to get an average.
None of it seems to be working. The sumIFS work fine and apparentlt COUNTIFs too but when I try to add the two counts it gives me run-time error 13 Type mismtach.

CountLocked2011 = Application.CountIfs(AverageRange, StateRange, "<>Draft", SubmitRange, ">=1/1/2011", SubmitRange, "<6/1/2011", LockedRange, "Yes") 
CountSpaceLocked2011 = Application.CountIfs(AverageRange, StateRange, "<>Draft", SubmitRange, "", LockedRange, "Yes")
 
[B]CountSpaceLocked2011 = CountSpaceLocked2011 + CountLocked2011[/B]

Please help resolve this run-time error or let me know how can I use an OR condition in AVERAGEFS.

 

[baitmaster]

Have you checked the values being returned in lines 1 and 2?

If the first two lines are working ok, but the third is failing due to type mismatch, you could try forcing VBA to see them as integers (or other types, as required)

Try wrapping each varable in cInt, i.e. CountSpaceLocked2011 = cint(CountSpaceLocked2011) + cint(CountLocked2011)

 

[beedistinct]

Have you checked the values being returned in lines 1 and 2?

If the first two lines are working ok, but the third is failing due to type mismatch, you could try forcing VBA to see them as integers (or other types, as required)

Try wrapping each varable in cInt, i.e. CountSpaceLocked2011 = cint(CountSpaceLocked2011) + cint(CountLocked2011)

Thanks! I relooked at my formula. The syntax was wrong for COUNTIFS, I was using the first parameter as an AverageRange which is not required. COUNTIFS only requires the Criteria Range.

Thank you!