
Originally Posted by
ma
I am unable to use this month function in Countifs
Correct.
COUNTIFS is really no different from COUNTIF in so far as you can not adapt the values being evaluated as part of the calculation (unlike SUMPRODUCT)
Applying a MONTH function to a date is an adaptation of the underlying value - therefore not possible with COUNTIF(S)
(remember you'd have to test YEAR also)

Originally Posted by
ma
I solved it by using >=01-01-10 and <=31-01-10 criteria but i don't think its nice idea ....
It might not be a nice idea but using COUNTIFS it is the only viable approach
(obviously you could make the dates adapt per the headers)
In summary, based on your sample file...
COUNTIFS
SUMPRODUCT
or if you wish to conduct one date based date (and account for possibility of non-numerics)
goes without saying the SUMPRODUCTs will be slower.
Given the volume of data... if you want a really quick approach... I would say you'd be better off adding a "key" to System Data tab, eg:
You can then use a much (much much) more efficient COUNTIF test given you now have only one condition (based on Key column), eg:
The final route - though involving more formulas - will be significantly quicker to calculate than the SUMPRODUCT approaches - and still quicker than the SUMIFS approach.
Bookmarks