CONDITONAL SUM DATE RANGE ISSUE

[woody]

Having an issue with suming using the Conditional sum wizard. At the bottom
is the formula.

Have 3 criteria, a specific code number (shown as V13 below ) and also fall
between 2 dates, The date criteria is typically like: >=1/1/08 & <=1/31/08
shown as <=W13 and >=X13.

The problem is that it always returns zero as the sum and I know its not zero.

If I change the date criteria to just >=1/1/08 (>=X13) it will work but
returns the sum for all the dates equal to or greater than 1/1/08.

Any suggestions would be helpful?

=SUM(IF($B$2:$B$1423=V13,IF($A$2:$A$1423<=W13,IF($A$2:$A$1423>=X13,$H$2:$H$1423,0),0),0))

 

[Peo Sjoblom]

You need to enter the formula with Ctrl + Shift & Enter


You can also rewrite the formula as

=SUMPRODUCT(--($B$2:$B$1423=V13),--($A$2:$A$1423<=W13),--($A$2:$A$1423>=X13),$H$2:$H$1423)


and enter it normally

--


Regards,


Peo Sjoblom

 

[woody]

Peo,

Thanks for the reply.

I use the conditional sum wizard regularily, but noit with the date range.

I tried making some adjustments and know to use Ctrl + Shift & Enter. Still
not working.

Tried plugging in your string and still get a zero sum??

 

[Peo Sjoblom]

If that happens some (maybe all) of your dates are probably text and not
numbers so you are comparing
text with numbers and text is always greater.

Use

=ISTEXT(A2) (I am assuming A2:A4123 are the dates)

copy down and if you get any TRUE values then they are text

do the same for V13 and W13

Post back if you have text values and describe how the dates look
(04/15/2008 etc)

--


Regards,


Peo Sjoblom

 

[ooophelia]

Hope this helps. I use date ranges as well, and here's how my formula works:

(Let column A be the date range you are searching, and B be the result. I
just used an arbitrary date range).

=SUMIF(A1:A10, ">=8/4/08", B1:B10)-SUMIF(A1:A10, ">8/8/08", B1:B10)

It works like a charm!

 

[Peo Sjoblom]

What about this part?

$B$2:$B$1423=V13

--


Regards,


Peo Sjoblom

 

[woody]

Peo

Came back TRUE,

the cell properties is set to Date.

 

[woody]

Here is a brief cut of what I am trying to do. There are 3,000 lines.

The conditions are:
- Code
- Date range
and then Sum TTL $

DATE Code TTL $
1/8/2008 1050 $552.00
2/18/2008 1225 $820.00
2/12/2008 1234 $501.00
3/12/2008 1050 $201.00
5/9/2008 1456 $991.00
2/3/2008 1225 $405.00

 

[woody]

V13 is False as text, it is the Code no.

What about this part?

$B$2:$B$1423=V13

--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

OK, then they are text and that would explain the zero result

How do the dates look? US date format like 08/08/08?

Check if you have trailing invisible characters, do you get an 8 if the date
looks like above if you use

=RIGHT(A2,1)

if you get a blank cell change the formula to

=CODE(RIGHT(A2,1))


Make the column size wider and you will see that they are left aligned the
way text are
then make sure the column is formatted as dates and then select column A,
then do data>text to columns and click finish


If they became right aligned they have converted to numerical Excel dates
and your formula should work (both formulas)






--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

I was asking ooophelia because her formula wouldn't work since your
formula need more criteria than just the date range which her formula would
have worked for

--


Regards,


Peo Sjoblom