Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count wrong
I am trying to count the number of plan dates (column Z) to Actual Dates
(column AA). H2 is the date cell. The below formula give me the count of 16 which is right =SUMPRODUCT(--(TCS!$E$2:$E$1000="GDS"),--(TCS!$Z$2:$Z$1000=H2)) Give me a number of 44 which is not right, it should be 12. =IF(SUMPRODUCT(--(TCS!$E$2:$E$1000="GDS"),--(TCS!$Z$2:$Z$1000=H2),--(TCS!$AA $2:$AA$1000<""))<0,SUMPRODUCT(--(TCS!$Z$2:$Z$1000=H2),--(TCS!$AA$2:$AA $1000<"")),"") I've been scratching my head for a few hours, not sure what to do. Thanks again s2m -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200607/1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count wrong
Check to ensure there is no discrepancy between dates. 1/1/2006 12 AM and
1/1/2006 1 AM formatted just to show the day is a different number. -- http://HelpExcel.com 1-888-INGENIO 1-888-464-3646 x0197758 "s2m via OfficeKB.com" wrote: I am trying to count the number of plan dates (column Z) to Actual Dates (column AA). H2 is the date cell. The below formula give me the count of 16 which is right =SUMPRODUCT(--(TCS!$E$2:$E$1000="GDS"),--(TCS!$Z$2:$Z$1000=H2)) Give me a number of 44 which is not right, it should be 12. =IF(SUMPRODUCT(--(TCS!$E$2:$E$1000="GDS"),--(TCS!$Z$2:$Z$1000=H2),--(TCS!$AA $2:$AA$1000<""))<0,SUMPRODUCT(--(TCS!$Z$2:$Z$1000=H2),--(TCS!$AA$2:$AA $1000<"")),"") I've been scratching my head for a few hours, not sure what to do. Thanks again s2m -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200607/1 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count wrong
I checked that changed the format and I still get the wrong number. Any
other ideas? galimi wrote: Check to ensure there is no discrepancy between dates. 1/1/2006 12 AM and 1/1/2006 1 AM formatted just to show the day is a different number. I am trying to count the number of plan dates (column Z) to Actual Dates (column AA). H2 is the date cell. [quoted text clipped - 12 lines] s2m -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200607/1 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count wrong
The number i get is correct if I don't use --(TCS!$F$2:$F$1000="GDS". Is it
possible to have to many condtions? Does the order of the condtions make a difference? galimi wrote: Check to ensure there is no discrepancy between dates. 1/1/2006 12 AM and 1/1/2006 1 AM formatted just to show the day is a different number. I am trying to count the number of plan dates (column Z) to Actual Dates (column AA). H2 is the date cell. [quoted text clipped - 12 lines] s2m -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200607/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
Count and Count if funtions | Excel Worksheet Functions | |||
count cell if value present in every other cell + criteria | Excel Worksheet Functions | |||
Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Advanced Count functions | Excel Discussion (Misc queries) |