CONDITONAL SUM DATE RANGE ISSUE
 

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

CONDITONAL SUM DATE RANGE ISSUE
 

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" wrote in message
...
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))

CONDITONAL SUM DATE RANGE ISSUE
 

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" wrote:

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" wrote in message
...
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))

CONDITONAL SUM DATE RANGE ISSUE
 

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

"woody" wrote in message
...
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" wrote:

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" wrote in message
...
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))

CONDITONAL SUM DATE RANGE ISSUE
 

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!



"woody" wrote:

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

CONDITONAL SUM DATE RANGE ISSUE
 

What about this part?

$B$2:$B$1423=V13

--


Regards,


Peo Sjoblom

"ooophelia" wrote in message
...
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!



"woody" wrote:

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

CONDITONAL SUM DATE RANGE ISSUE
 

Peo

Came back TRUE,

the cell properties is set to Date.

"Peo Sjoblom" wrote:

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

"woody" wrote in message
...
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" wrote:

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" wrote in message
...
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))

CONDITONAL SUM DATE RANGE ISSUE
 

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

The conditions a
- 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



"Peo Sjoblom" wrote:

What about this part?

$B$2:$B$1423=V13

--


Regards,


Peo Sjoblom

"ooophelia" wrote in message
...
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!



"woody" wrote:

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

CONDITONAL SUM DATE RANGE ISSUE
 

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

"Peo Sjoblom" wrote:

What about this part?

$B$2:$B$1423=V13

--


Regards,


Peo Sjoblom

"ooophelia" wrote in message
...
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!



"woody" wrote:

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

CONDITONAL SUM DATE RANGE ISSUE
 

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

"woody" wrote in message
...
Peo

Came back TRUE,

the cell properties is set to Date.

"Peo Sjoblom" wrote:

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

"woody" wrote in message
...
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" wrote:

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" wrote in message
...
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))

CONDITONAL SUM DATE RANGE ISSUE
 

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

"woody" wrote in message
...
V13 is False as text, it is the Code no.

"Peo Sjoblom" wrote:

What about this part?

$B$2:$B$1423=V13

--


Regards,


Peo Sjoblom

"ooophelia" wrote in message
...
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!



"woody" wrote:

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