Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with dates
How do I change the formula below so that it works with dates?
=SUMPRODUCT(--($E$2:$E$1043="CA*"),--($D$2:$D$1043=31/12/1999)) as it returns '0' at the moment whereas there should be 100's Essentially, count the number of instances 'CA' appears in column E when the date is on or after 31/12/99. -- Traa Dy Liooar Jock |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with dates
$D$2:$D$1043=--"31/12/1999"
-- ** John C ** Please remember, if your question is answered, to check the YES box below. It helps everyone. "Jock" wrote: How do I change the formula below so that it works with dates? =SUMPRODUCT(--($E$2:$E$1043="CA*"),--($D$2:$D$1043=31/12/1999)) as it returns '0' at the moment whereas there should be 100's Essentially, count the number of instances 'CA' appears in column E when the date is on or after 31/12/99. -- Traa Dy Liooar Jock |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with dates
=SUMPRODUCT(--($E$2:$E$1043="CA*"),--($D$2:$D$1043=date(1999,12,31))
Did you really mean to include Dec 31, 1999? If no, you could just check the year: =SUMPRODUCT(--($E$2:$E$1043="CA*"),--(year($D$2:$D$1043)2000)) Jock wrote: How do I change the formula below so that it works with dates? =SUMPRODUCT(--($E$2:$E$1043="CA*"),--($D$2:$D$1043=31/12/1999)) as it returns '0' at the moment whereas there should be 100's Essentially, count the number of instances 'CA' appears in column E when the date is on or after 31/12/99. -- Traa Dy Liooar Jock -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct with dates | New Users to Excel | |||
sumproduct & dates | Excel Worksheet Functions | |||
SUMPRODUCT - DIFFERENT DATES | Excel Worksheet Functions | |||
sumproduct between dates | Excel Worksheet Functions | |||
SUMPRODUCT ON DATES | Excel Worksheet Functions |