Conditional Sum date range

[woodyjr]

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 are:

- 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?

 

[ShaneDevenshire]

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

 

[Peo Sjoblom]

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 data>text to columns and press finish

Make sure you widen the column, text is left aligned and numbers (true
dates) right



--


Regards,


Peo Sjoblom