New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Count Non-Blank Cells Between a Date Range

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Count Non-Blank Cells Between a Date Range

Postby JABJC01 » Sun Dec 18, 2016 2:50 pm

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

Postby Kevin UK » Mon Dec 19, 2016 1:12 am

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

Postby JABJC01 » Mon Dec 19, 2016 9:06 am

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

Postby Kevin UK » Tue Dec 20, 2016 3:18 am

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

Postby JABJC01 » Tue Dec 20, 2016 5:41 pm

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


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 65 guests