| Home |
| Search |
| Today's Posts |
|
#1
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
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
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
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
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
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
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions | |||
| Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
| Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
| Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Linear Mode
