Quote 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)

Quote 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

B14:
=COUNTIFS('System Data'!$C$2:$C$6020,">="&B$13,'System Data'!$C$2:$C$6020,"<"&EDATE(B$13,1),'System Data'!$D$2:$D$6020,$B$12,'System Data'!$E$2:$E$6020,$A14)
applied across matrix B14:P18

SUMPRODUCT

'Data Tables'!B14:
=SUMPRODUCT(--('System Data'!$C$2:$C$6020>=B$13),--('System Data'!$C$2:$C$6020<EDATE(B$13,1)),--('System Data'!$D$2:$D$6020=$B$12),--('System Data'!$E$2:$E$6020=$A14))
applied across matrix

or if you wish to conduct one date based date (and account for possibility of non-numerics)

'Data Tables'!B14:
=SUMPRODUCT(--(TEXT('System Data'!$C$2:$C$6020,"MMYYYY")=TEXT(B$13,"MMYYYY")),--('System Data'!$D$2:$D$6020=$B$12),--('System Data'!$E$2:$E$6020=$A14))
applied across matrix

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:

'System Data'!F2:
=$C2-DAY($C2)+1&"@"&$D2&"@"&$E2
copied down for all rows

You can then use a much (much much) more efficient COUNTIF test given you now have only one condition (based on Key column), eg:

'Data Tables'!B14: 
=COUNTIF('System Data'!$F$2:$F$6020,B$13&"@"&$B$12&"@"&$A14)
copied across matrix

The final route - though involving more formulas - will be significantly quicker to calculate than the SUMPRODUCT approaches - and still quicker than the SUMIFS approach.