COUNTIFS return 0

[datawiz]

Hi,

I have an Excel sheet where I have unique numbers on col A, dates on col B, and numbers ($) on col D.

I want to count where month is june (col B) and <=$100 on col D.

I am using =COUNTIFS($B$3:$B$300, (MONTH($B$3:$B$300)=6), $D$3:$D$300, "<=100") but the formula returns 0 where I must have some number, I filtered and actually counted. Where am I doing wrong?

Thank you in advance!

 

[barry houdini]

You can't use MONTH function like that in COUNTIFS, try using SUMPRODUCT, e.g.

=SUMPRODUCT((MONTH($B$3:$B$300)=6)*($D$3:$D$300<=100))

 

[datawiz]

THANK YOU SO MUCH!!!! I works great; I wasn't aware of the restrictions of COUNTIFS.

 

[datawiz]

One more quick question, if there are breaks in my date columns (empty cells), would it still work?

 

[barry houdini]

Excel regards empty cells as January dates, MONTH(cell)=1, so it's not a problem to have blanks as long as you aren't counting January dates - when you count those you need an extra check to make sure they aren't blank, i.e.

=SUMPRODUCT((MONTH($B$3:$B$300)=1)*($D$3:$D$300<=100)*($B$3:$B$300<>""))

 

[datawiz]

Thank you once again! I pay attention to January dates, yet when I copy/paste my column with its values, I have no problem and it calculates perfectly. However, with the blanks in the original column I get #VALUE error; do you have any idea why?