# Premium Excel Course Now Available!

## Count Non-Blank Cells Between a Date Range

Formulas, Functions, Formatting, Charts, Data Analysis, etc.
Forum rules
This forum is closed.

All new posts should be made at our new Excel Forum at TeachExcel.com.

### Count Non-Blank Cells Between a Date Range

I need some help with a formula. I'm trying to count the NOT BLANK cells in range C4:C17 that are between 8/1/2016 and 8/31/2016 (cells B4:B17). My formula is returns a value of 30, I'm expecting an answer of 5. I would appreciate help correcting my formula. See attached.

Here's my formula in F4: =COUNTA(C4:C17,B4:B17,">="&E4,DATE,"<="&EOMONTH(E4,0))

Thank you.

Excel.jpg
• 0

You do not have the required permissions to view the files attached to this post.
Last edited by JABJC01 on Tue Dec 20, 2016 5:43 pm, edited 1 time in total.
JABJC01
Rookie

Posts: 10
Joined: Sep 4, 2015
Reputation: 0
Excel Version: 2010

### Re: Count Non-Blank Cells Between a Date Range

Hi

Without a proper spreadsheet, are those dates in E4 & F4 proper dates or are they text and numbers?

The count of those in B4:B17 which = August and C4:C17 not blank is 5?

Try the following:

=SUMPRODUCT(--(TEXT(B4:B17,"mmmm")=E4),--(C4:C17<>""))
• 0

Regards

Kevin
Kevin UK
Excel Master

Posts: 568
Joined: Jun 24, 2012
Reputation: 86
Excel Version: 2016

### Re: Count Non-Blank Cells Between a Date Range

Kevin,

Yes, the dates in E4 and F4 are proper dates. I used a custom format for both. I'm sorry for the confusion that caused.

I attached a copy of the spreadsheet. I hope you can still help me with this.

Thank you
• 0

You do not have the required permissions to view the files attached to this post.
JABJC01
Rookie

Posts: 10
Joined: Sep 4, 2015
Reputation: 0
Excel Version: 2010

### Re: Count Non-Blank Cells Between a Date Range

Hi

Do you want the count of dates between E1 (01/08/2016) & 22/08/2016 & non blank cells in C4:C17? =3

If so, try:
Code: Select all
`=SUMPRODUCT(--(B4:B17>=E4),--(B4:B17<=DATE(YEAR(E4),MONTH(E4),F4)),--(C4:C17<>""))`

If you want the count for all of August & non blank cells in C4:C17? =5

Try:
Code: Select all
`=SUMPRODUCT(--(MONTH(B4:B17)=MONTH(E4)),--(C4:C17<>""))`
• 5

Regards

Kevin
Kevin UK
Excel Master

Posts: 568
Joined: Jun 24, 2012
Reputation: 86
Excel Version: 2016

### Re: Count Non-Blank Cells Between a Date Range

Kevin,

Thank you. Your second option is exactly what I needed.
• 0

JABJC01
Rookie

Posts: 10
Joined: Sep 4, 2015
Reputation: 0
Excel Version: 2010