Count according to date & exclude empty cells

**Lukael** · 10-05-2015, 07:47 AM

Hi,

I have Dates in column A (from 1st to 31th January), and values in column B, which corresponds to a day value.

I want to have number of rows in column B that are not empty according to January date. I need this for all months, and all dates are in column A.

Please help, this is struggling me for quite a while !!

See attached sample !

**mangesh.mehendale** · 10-05-2015, 07:52 AM

I dont understand why dont you applying Count formula to Col B

**samba_ravi** · 10-05-2015, 07:53 AM

=COUNTIFS($A$1:$A$31,">="&DATE(2015,1,1),$A$1:$A$31,"<"&DATE(2015,2,1),$B$1:$B$31,"<>"&"")

Please Login or Register  to view this content.

Try this formula

**NeedForExcel** · 10-05-2015, 07:54 AM

But why not =COUNTA(B:B)

Am I missing something?

**cbatrody** · 10-05-2015, 07:56 AM

OR,

=COUNTIFS(A:A,">"&EOMONTH(D3,-1),A:A,"<="&EOMONTH(D3,0),B:B,"<>"&"")

See the attached file.

**Lukael** · 10-05-2015, 08:21 AM

nflsales,

Great formula, but what about december, It doesn't work ?

**mangesh.mehendale** · 10-05-2015, 08:22 AM

If you want to count as per month then here the formula
Ctrl+Shift+Enter

Formula:

Please Login or Register  to view this content.

**samba_ravi** · 10-05-2015, 08:24 AM

Try with this for December
=COUNTIFS($A$1:$A$31,">="&DATE(2015,12,1),$A$1:$A$31,"<"&DATE(2016,1,1),$B$1:$B$31,"<>"&"")

**Tony Valko** · 10-05-2015, 09:44 AM

Try this...

Use cells to hold the date criteria.

D1 = the 1st of the month date for the month to count

Data Range

	A	B	C	D	E
1	1/1/2015	1		1/1/2015	30
2	1/2/2015	33112	------	------	------
3	1/3/2015	1213
4	1/4/2015	11
5	1/5/2015
6	1/6/2015	1234243
7	1/7/2015	345
8	1/8/2015	4343
9	1/9/2015	4345
10	1/10/2015	939384

This formula entered in E1:

=COUNTIFS(A:A,">="&D1,A:A,"<="&EOMONTH(D1,0),B:B,"<>")

You may have to replace the commas with semi-colons.

**Lukael** · 10-06-2015, 01:32 AM

Wow, all these options and all of them work !

Thanks guys, you helped me a lot, I used solution from cbatrody, It suits my needs

nflsales, your solution works for December too, but my worksheet is designed differently so I can't use It !

Big thumbs for all of you, this formula drived me nuts !

**cbatrody** · 10-06-2015, 01:41 AM

You are welcome

If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.

**Tony Valko** · 10-06-2015, 04:36 AM

You're welcome. We appreciate the feedback!

**Tony Valko** · 10-06-2015, 04:39 AM

Tip...

I noticed in a couple of the replies the use of this expression for the "is not blank" criteria:

"<>"&""

That can be reduced to:

"<>"

Count according to date & exclude empty cells

LinkBack

Thread Tools

Rate This Thread

Display

Count according to date & exclude empty cells

Re: Count according to date & exclude empty cells

Re: Count according to date & exclude empty cells

Re: Count according to date & exclude empty cells

Re: Count according to date & exclude empty cells

Re: Count according to date & exclude empty cells

Re: Count according to date & exclude empty cells

Re: Count according to date & exclude empty cells

Re: Count according to date & exclude empty cells

Re: Count according to date & exclude empty cells

Re: Count according to date & exclude empty cells

Re: Count according to date & exclude empty cells

Re: Count according to date & exclude empty cells

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Exclude empty cells from a calculation

VBA modification to exclude empty cells, just need help implementing!!

Excel 2007 : exclude empty cells

Exclude empty cells

Excel 2007 : how to Calculate formula to exclude empty cells

How do I exclude empty cells from being printed?

How to exclude cells that are empty?

Bookmarks

Bookmarks

Posting Permissions