How to count non blank cells with a condition?
 

Hi all,

I need to count non blank cells in a column where the date for that row (but different column) falls between two dates

For Example...

_______A_________B

1__05/04/2008___TEXT
2__16/10/2011___
3__29/03/2010___
4__09/12/2011___TEXT
5__01/01/2010___TEXT

I need to count all of the non blank cells in column B where the date in Column A is between or equal to 01/04/2008 & 31/03/2010.

In the example there is only B3 that matches this criteria so the formula should return 1.

Anyone know how to do that?

Many thanks

Jason

Perhaps I should elaborate...

I know how to count all of the dates in column A that fall between or are equal to 01/04/2008 & 31/03/2010 by putting these dates in say cell D1 & D2 and then the use of a sum product...

=SUMPRODUCT((A1:A5<=d2)*(A1:A5=D1))

And I also know how to count all of the blank cells in column B with a simple counta...

=COUNTA(B1:B5)


So I need to basically combine the two of them. But that's where I get lost.

How to count non blank cells with a condition?
 

Hi Jason,

Am Wed, 15 Aug 2012 12:36:45 +0000 schrieb Jay07:

_______A_________B

1__05/04/2008___TEXT
2__16/10/2011___
3__29/03/2010___
4__09/12/2011___TEXT
5__01/01/2010___TEXT

I need to count all of the non blank cells in column B where the date in
Column A is between or equal to 01/04/2008 & 31/03/2010.

try:
=SUMPRODUCT(--(LEN(B1:B100)0),--(A1:A100=DATE(2008,4,1)),--(A1:A100<=DATE(2010,3,31)))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thank you Claus, that seems to be what I'm looking for.

It worked fine when applying it to the example I used however when I've tried adapting it to the workbook I'm trying to use this in it keeps reading '0' and I've no idea why. Mind having a look and tell me if it's anything obvious...

=SUMPRODUCT((LEN('PM Orders'!$N$3:$N$65277)0)*('PM Orders'!$O$3:$O$65277=DATE(2008,4,1))*('PM Orders'!$O$3:$O$65277<=DATE(2010,3,31)))

The only thing I can think of is that the cells included in the range N3:N65277 I'm using an indirect data validation with a drop down list of options - that shouldn't effect it though should it??

Never mind, the formula worked just fine. What it was is that I had changed the time/date system from 1904 to 1907 which then screwed with all my dates in column O.

The formula was right though which is why it was showing '0'


Thanks!