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!

 

[AhoyNC]

Try:

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

or just

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

 

[halloyd]

Try:

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

or just

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

thanks for the reply!

Unfortunately another Value error using both ):

 

[AhoyNC]

Make sure that your cells
Active Claims'!$F1:$F500)are actual Excel dates (numeric) and not text.

 

[halloyd]

Make sure that your cells
Active Claims'!$F1:$F500)are actual Excel dates (numeric) and not text.

Oh... Wow, I feel like the reason IT people talk to their customers like they're stupid.

Thank you! That fixed it!

So here's the thing: this list is going to keep expanding. Is there a way for it to work on blank cells, ignoring them but detecting any added dates in the future?

 

[AhoyNC]

Unless I'm missing something, blanks shouldn't be a problem. See example below with blank between dates and after the dates.
Another option would be to turn your data into a table and the formula range will expand as you add data.

Links to making data into a table.
https://www.contextures.com/xlExcelTable01.html
https://support.office.com/en-us/ar...l-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c

Excel Workbook
	A	B	C
1	Date		Count of 9th month
2	2/5/2018		3
3	4/2/2018
4
5	9/2/2018
6	6/5/2008
7	9/12/2018
8	9/10/2018
9	10/12/2018
Sheet

 

[Aladin Akyurek]

If the year is not important...

Control+shift+enter, not just enter:

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

If you want to fix the count to a certain year, say to 2018, control+shift+enter, not just enter:

=SUM(IF(ISNUMBER('[2018 Claim Status Sheet.xlsx]Active Claims'!$F1:$F500),IF('[2018 Claim Status Sheet.xlsx]Active Claims'!$F1:$F500-DAY('[2018 Claim Status Sheet.xlsx]Active Claims'!$F1:$F500)+1=DATE(2018,9,1),1)))

 

[halloyd]

Awesome, thank you both!!

 

[Aladin Akyurek]

Awesome, thank you both!!

Glad to help. The first formula misses the month test:

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

 

[halloyd]

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?