countifs showing no results

[aamir]

i using following formula but it returns no results

COUNTIFS($O:$O,S16,$B:$B,R16,$P:$P,$T$15)

O:O, is list of names S16, is specific name B:B, is Items list R16, is specific item P:P, is only month (mmm) derived from a date column (dd/mm/yy) T15, is month [Jan, Feb, Mar,......Dec] i think i have some problem with date in last selection.

 

[jasonb75]

The month and date would not be recognised by couintifs in the way you need, try

=SUMPRODUCT(($O:$O=S16)*($B:$B=R16)*(TEXT($P:$P,"mmm")=$T$15))

Not sure how your sheet will cope with full columns in that formula so you might be better off restricting it to populated ranges.

 

[Aladin Akyurek]

i using following formula but it returns no results

COUNTIFS($O:$O,S16,$B:$B,R16,$P:$P,$T$15)

O:O, is list of names S16, is specific name B:B, is Items list R16, is specific item P:P, is only month (mmm) derived from a date column (dd/mm/yy) T15, is month [Jan, Feb, Mar,......Dec] i think i have some problem with date in last selection.

Let's suppose we are interested in obtaining a count for the May month of 2010...

Let T15 house a first day date like 1-May-2010.

Now invoke:

=COUNTIFS($O:$O,S16,$B:$B,R16,$P:$P,">="&$T$15,$P:$P,"<"&EDATE($T$15,1))

 

[aamir]

The month and date would not be recognised by couintifs in the way you need, try

=SUMPRODUCT(($O:$O=S16)*($B:$B=R16)*(TEXT($P:$P,"mmm")=$T$15))

Not sure how your sheet will cope with full columns in that formula so you might be better off restricting it to populated ranges.

It takes hell of time to calculate...

 

[barry houdini]

Did you try Aladin's suggestion, that'll be quicker......

 

[aamir]

Yeah! Aladin's suggestion is good!!! it works...