Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum date range
I am trying to run the conditional sum wizard and have 2 sets of criteria but
keep coming back with a zero sum. The data has 3 columns. A rep code, dates and total dollars sold. The dates are between 1/1/2008 to 10/1/2008 The 2 sets of criteria a - The rep code (4 digit number), shown in the formula below as S2, found in $B$2:$B$1423. - The date range which I narrow to a 1 month spread. e.g.=1/1/2008 and <2/1/2008 (shown as T2 and U2) It then should ttl the sales dollar (found in column H2 thru H1423 as shown in the formula below) for each date range and by the rep code. It always returns a zero dollar amount. If I remove one of the date ranges e.g. just put 1/1/2008 it will return a valid dollar amount for all sales greater than 1/1/2008. As soon as I add the other date criteria I get a zero dollar amount. Here is the formula: {=SUM(IF($B$2:$B$1423=S2,IF($A$2:$A$1423=T2,IF($A $2:$A$1423<U2,$H$2:$H$1423,0),0),0))} Any suggestions? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum date range
Hi,
try =SUMPRODUCT(--($B$2:$B$1423=S2),--($A$2:$A$1423<U2),--($A$2:$A$1423=T2),$H$2:$H$1423) -- Thanks, Shane Devenshire "woodyjr" wrote: I am trying to run the conditional sum wizard and have 2 sets of criteria but keep coming back with a zero sum. The data has 3 columns. A rep code, dates and total dollars sold. The dates are between 1/1/2008 to 10/1/2008 The 2 sets of criteria a - The rep code (4 digit number), shown in the formula below as S2, found in $B$2:$B$1423. - The date range which I narrow to a 1 month spread. e.g.=1/1/2008 and <2/1/2008 (shown as T2 and U2) It then should ttl the sales dollar (found in column H2 thru H1423 as shown in the formula below) for each date range and by the rep code. It always returns a zero dollar amount. If I remove one of the date ranges e.g. just put 1/1/2008 it will return a valid dollar amount for all sales greater than 1/1/2008. As soon as I add the other date criteria I get a zero dollar amount. Here is the formula: {=SUM(IF($B$2:$B$1423=S2,IF($A$2:$A$1423=T2,IF($A $2:$A$1423<U2,$H$2:$H$1423,0),0),0))} Any suggestions? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum date range
You can write it like this as well
=SUMPRODUCT(--($B$2:$B$1423=S2),--($A$2:$A$1423=T2),--($A$2:$A$1423<U2),$H$2:$H$1423) or this =SUMPRODUCT(--(YEAR($B$2:$B$1423)=S2),--(MONTH($A$2:$A$1423)=T2),--($A$2:$A$1423<U2),$H$2:$H$1423) where you would put the year in S2 (2008) and the month in T2 (1 for January) None of the above needs to be entered with ctrl + shift & enter However it shouldn't explain the incorrect result you get I suspect the some dates are text numbers, they will always be greater than any number so of you use 2/1/08 in U2 and the dates in A are text it will fail since they are greater than 2/1/08 regardless if they look like 01/15/08. Try this select A2:A1423 (type in A2:A1423 in the name box above column A's header and press enter) then press F5, click special and select constants (or formulas if they are derived from formulas) and clear everything but text and click OK, if any of those dates are selected then they are text. You can also use =ISTEXT(A2) copy down if you get TRUE then it is text. If they are text, select the whole column and format as date then do datatext to columns and press finish Make sure you widen the column, text is left aligned and numbers (true dates) right -- Regards, Peo Sjoblom "woodyjr" wrote in message ... I am trying to run the conditional sum wizard and have 2 sets of criteria but keep coming back with a zero sum. The data has 3 columns. A rep code, dates and total dollars sold. The dates are between 1/1/2008 to 10/1/2008 The 2 sets of criteria a - The rep code (4 digit number), shown in the formula below as S2, found in $B$2:$B$1423. - The date range which I narrow to a 1 month spread. e.g.=1/1/2008 and <2/1/2008 (shown as T2 and U2) It then should ttl the sales dollar (found in column H2 thru H1423 as shown in the formula below) for each date range and by the rep code. It always returns a zero dollar amount. If I remove one of the date ranges e.g. just put 1/1/2008 it will return a valid dollar amount for all sales greater than 1/1/2008. As soon as I add the other date criteria I get a zero dollar amount. Here is the formula: {=SUM(IF($B$2:$B$1423=S2,IF($A$2:$A$1423=T2,IF($A $2:$A$1423<U2,$H$2:$H$1423,0),0),0))} Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format over Date Range | Excel Discussion (Misc queries) | |||
SumIf conditional to date range | Excel Worksheet Functions | |||
Date Range Conditional Formatting | Excel Worksheet Functions | |||
Conditional formatting and Highlighting Date Range | Excel Discussion (Misc queries) | |||
Conditional formatting and Highlighting Date Range | Excel Discussion (Misc queries) |