Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Quote:
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
|
|||
|
|||
Quote:
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?? |
#5
|
|||
|
|||
Quote:
The formula was right though which is why it was showing '0' Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting non blank cells according to a condition | Excel Discussion (Misc queries) | |||
count cf cells displaying condition 3 | Excel Discussion (Misc queries) | |||
Count cells with condition | Excel Worksheet Functions | |||
Count filtered cells meeting a condition | Excel Discussion (Misc queries) | |||
how do I count pairs of cells when each matches a condition? | Excel Discussion (Misc queries) |