|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 9th, 2010, 02:31 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If count
start date end date
01/01/2010 01/01/2010 01/01/2010 01/01/2010 01/01/2010 01/01/2011 01/01/2010 01/01/2010 I want to count the start date column only if there is nothing in the end date column. 
|
|
#2
February 9th, 2010, 02:34 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If count
Please help! I only have 1/2 an hour to figure this out.
"js20217075" wrote: start date end date 01/01/2010 01/01/2010 01/01/2010 01/01/2010 01/01/2010 01/01/2011 01/01/2010 01/01/2010 I want to count the start date column only if there is nothing in the end date column.
|
|
#3
February 9th, 2010, 03:32 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If count
The clearer the question, the faster the response, and vice versa. Things
that help are the version of Excel you are using, and where the data is (column A, B, etc.). For this, I've assumed your start date is in A, and your end date is in B. For all entries in A, where there is no entry in B: =sumproduct(--(a1:a1000""),--(b1: b1000="")) Adjust the range to suit. For specific entries in A, with no entry in B: =sumproduct(--(a1:a1000=date(2010,1,1)),--(b1:b1000="")) Regards, Fred "js20217075" wrote in message ... Please help! I only have 1/2 an hour to figure this out. "js20217075" wrote: start date end date 01/01/2010 01/01/2010 01/01/2010 01/01/2010 01/01/2010 01/01/2011 01/01/2010 01/01/2010 I want to count the start date column only if there is nothing in the end date column.
|
|
#4
February 9th, 2010, 03:59 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If count
Try
=SUMPRODUCT((A1:A100"")*(B1:B100="")) -- Jacob "js20217075" wrote: start date end date 01/01/2010 01/01/2010 01/01/2010 01/01/2010 01/01/2010 01/01/2011 01/01/2010 01/01/2010 I want to count the start date column only if there is nothing in the end date column.
|
|
#5
February 9th, 2010, 04:11 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If count
This isn't working. OMG do you know anything else to try?
"Jacob Skaria" wrote: Try =SUMPRODUCT((A1:A100"")*(B1:B100="")) -- Jacob "js20217075" wrote: start date end date 01/01/2010 01/01/2010 01/01/2010 01/01/2010 01/01/2010 01/01/2011 01/01/2010 01/01/2010 I want to count the start date column only if there is nothing in the end date column.
|
|
#6
February 9th, 2010, 04:15 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If count
Try one of these..
=SUMPRODUCT((A1:A1000)*(B1:B100=0)) =SUMPRODUCT((ISNUMBER(A1:A100))*(ISBLANK(B1:B100)) ) -- Jacob "js20217075" wrote: This isn't working. OMG do you know anything else to try? "Jacob Skaria" wrote: Try =SUMPRODUCT((A1:A100"")*(B1:B100="")) -- Jacob "js20217075" wrote: start date end date 01/01/2010 01/01/2010 01/01/2010 01/01/2010 01/01/2010 01/01/2011 01/01/2010 01/01/2010 I want to count the start date column only if there is nothing in the end date column.
|
|
#7
February 9th, 2010, 09:08 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If count
Apply filter on both the colums. On end date column remove tick mark
from end date column Blanks on the status bar you can see the number of cells being displayed. Else you can use =SUBTOTAL(3,A:A) in any of the visible cells it would return the count. A third option can be =COUNTA(A1:A100)-COUNTBLANK(B1:B100) Hope that helps, Anand On Feb 9, 9:11*am, js20217075 wrote: This isn't working. *OMG do you know anything else to try? "Jacob Skaria" wrote: Try =SUMPRODUCT((A1:A100"")*(B1:B100="")) -- Jacob "js20217075" wrote: start date * * * * * * * * end date 01/01/2010 * * * * * * * 01/01/2010 01/01/2010 01/01/2010 01/01/2010 * * * * * * * 01/01/2011 01/01/2010 01/01/2010 I want to count the start date column only if there is nothing in the end date column.- Hide quoted text - - Show quoted text -
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
