Counting months ignoring blanks and text

[RicardoCubed]

Hi. I have a file with a column of dates that is interrupted with both blanks and text. I want to ignore both the blanks and text and calculate how many records match each month of the year. I have the formula to ignore blanks but am stuck on ignoring text. Help please

[TABLE="width: 113"]
<tbody>[TR]
[TD]=SUMPRODUCT(--(Detail!$CC$3:$CC$243<>""),--(MONTH(Detail!$CC$3:$CC$243)=A75))[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[TABLE="width: 113"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[AhoyNC]

See if this works.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

=SUMPRODUCT(--(MONTH(IF(ISNUMBER(Detail!$CC$3:$CC$243),Detail!$CC$3:$CC$243))=A75))

Example below.

Excel Workbook
	A	B	C	D
1	Date		Month to count	5
2	5/1/2014			2
3	6/1/2014
4	6/5/2014
5
6
7	5/20/2014
8	7/12/2014
9	Text1
10	Text2
11	12/1/1915
12	10/1/1919
Sheet

 

[RicardoCubed]

Thank you for your response but this suggestion doesnt work. It is still counting a blank cell as a 1. If you put a 1 in month to count the result is 4.
There must be a formula that allows you to ignore blanks and text in a column when performing sumif's, etc.
Any help appreciated!!!!!

 

[Aladin Akyurek]

Thank you for your response but this suggestion doesnt work. It is still counting a blank cell as a 1. If you put a 1 in month to count the result is 4.
There must be a formula that allows you to ignore blanks and text in a column when performing sumif's, etc.
Any help appreciated!!!!!

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(DateRange),IF(YEAR(DateRange)=2014,IF(MONTH(DateRange)=3,1))))

This would count all dates which fall in month 3 of 2014...

 

[RicardoCubed]

Well I can see why you have the MrExcel MVP designation!!! Thank you so much!!!! Such an elegant solution! I just modified the year to be any value great than to pull in all the birth years. Really appreciate it!!!!

 

[Aladin Akyurek]

Well I can see why you have the MrExcel MVP designation!!! Thank you so much!!!! Such an elegant solution! I just modified the year to be any value great than to pull in all the birth years. Really appreciate it!!!!

You are welcome.

 

[kreineke]

I am having the same issue. Date column contains dates, blanks, and "x"'s. The formula work for every month except for January. When criteria is met in column M, and column T is January, "blank", or "x" it counts as 1. When formula is set to =2 thru 12 (feb - dec), everything works great. When Month is set to =1, blanks and text are counted. here is my array formula. I am using ctrl+shift+enter for array.

{=SUMPRODUCT((F09P1!$M$116:$M$400="HYDK")*(--(MONTH(IF(ISNUMBER(F09P1!$T$116:$T$400),F09P1!$T$116:$T$400))=1)))}

Please help.

Kirk

 

[Aladin Akyurek]

I am having the same issue. Date column contains dates, blanks, and "x"'s. The formula work for every month except for January. When criteria is met in column M, and column T is January, "blank", or "x" it counts as 1. When formula is set to =2 thru 12 (feb - dec), everything works great. When Month is set to =1, blanks and text are counted. here is my array formula. I am using ctrl+shift+enter for array.

{=SUMPRODUCT((F09P1!$M$116:$M$400="HYDK")*(--(MONTH(IF(ISNUMBER(F09P1!$T$116:$T$400),F09P1!$T$116:$T$400))=1)))}

Please help.

Kirk

Control+shift+enter, not just enter:

=SUM(IF(F09P1!$M$116:$M$400="HYDK",
  IF(ISNUMBER(F09P1!$T$116:$T$400),
  IF(MONTH(F09P1!$T$116:$T$400)=1,1))))

The foregoing would pick out the January month of any year. If this is not intended, we need to add a year test to the formula:

=SUM(IF(F09P1!$M$116:$M$400="HYDK",
  IF(ISNUMBER(F09P1!$T$116:$T$400),
  IF(YEAR(F09P1!$T$116:$T$400)=2014,
  IF(MONTH(F09P1!$T$116:$T$400)=1,1)))))