Count the amount of times a month appears in a range

[halloyd]

I tried the formulas here, but they are all returning #VALUE .

https://www.mrexcel.com/forum/excel...umber-times-specific-month-appears-range.html

Please note that I am referencing a separate workbook ('[2018 Claim Status Sheet.xlsx]Active Claims') which has the dates in it.

This is an example of my info plugged into one of the formulas:

=SUMPRODUCT(--(MONTH('[2018 Claim Status Sheet.xlsx]Active Claims'!$F1:$F500)=9),'[2018 Claim Status Sheet.xlsx]Active Claims'!$F1:$F500<>"")

Thanks!

 

[Aladin Akyurek]

Thank you!

New question:
This formula is counting blanks as January (1), so it says I currently have a lot of claims from January open when I actually have none.

=SUMPRODUCT(--(MONTH('[2018 Claim Status Sheet.xlsx]Active Claims'!$G3:$G500)=1))

Any idea how to fix?

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER('[2018 Claim Status Sheet.xlsx]Active Claims'!$G3:$G500),IF(MONTH('[2018 Claim Status Sheet.xlsx]Active Claims'!$G3:$G500)=1,1)))


Just enter:

=SUMPRODUCT(--ISNUMBER('[2018 Claim Status Sheet.xlsx]Active Claims'!$G3:$G500),--(MONTH('[2018 Claim Status Sheet.xlsx]Active Claims'!$G3:$G500)=1))

Both are array-processing formulas and equally expensive.

 

[halloyd]

Thank you so much. I really appreciate it.

 

[Aladin Akyurek]

Thank you so much. I really appreciate it.

You are welcome.