Is there a way to add the numbers in row 2 that have the same date so that if
the result is Equal to, OR, minus 1 to 3 before, OR, plus 1 to 3 over (say
30), then diplay the result, otherwise display nothing?
Formula needs to be in row 3 and if the date are different, as in B1, then
the calculation would start over with the new date in the next column.
Desired results are shown in row 3
My attempts have resulted in formula that is too long.
A B C D
1 Date 8/29/02 11/6/05 11/6/05
2 Draft 16 20 7
3 New 16 27

Thank you
Luke

"Luke" wrote...
Is there a way to add the numbers in row 2 that have the same date
so that if the result is Equal to, OR, minus 1 to 3 before, OR,
plus 1 to 3 over (say 30), then diplay the result, otherwise
display nothing? Formula needs to be in row 3 and if the date are
different, as in B1, then the calculation would start over with
the new date in the next column.
Desired results are shown in row 3
My attempts have resulted in formula that is too long.
A B C D
1 Date 8/29/02 11/6/05 11/6/05
2 Draft 16 20 7
3 New 16 27

Would the row 1 dates always be ordered older to newer left to right? If so,
enter the following formula in B3.

B3:
=IF(COUNTIF($A$1:A$1,B$1)=0,SUMIF(B$1:$IV$1,B$1,B$ 2:$IV$2),"")

Fill B3 right as far as needed.

Harlan, Thanks for looking.
Yes that's right, date older to never.
Your formula is great for adding the Row 2 together progressively. However,
I need it to add the numbers in row 2 that have the same date, Then if the
result is Equal to, say 30 days, OR, minus 1 to 3 before, OR, plus 1 to 3
over 30 days, then diplay the result, otherwise display nothing.

For example, I have added columns to the original post.
Notice C2=20, C3="" then D2=7, D3=27 (3 days before total of 30 days), and
in E2=5, E3=32 but F2=2, F3="" (because the result would be 34, 1 day overthe
desired criteria)
e.g. In D2 the numbers 7 thru 13 would have results in D3 (30-3=27, 30-2=28,
30-1=29, 30-0=30, 30+1=31, 30+2=32 and 30+3=33). Anything else would result
in "".
A B C D
1 Date 8/29/02 11/6/03 11/8/03
2 Draft 16 20 7
3 New 16 27

A E F G
1 Date 12/5/03 12/9/03 11/6/05
2 Draft 5 2 7
3 New 32

I'll keep pestering it to see if I can get it to fester.
Thanks for your response Harlan
Luke

"Harlan Grove" wrote:

"Luke" wrote...
Is there a way to add the numbers in row 2 that have the same date
so that if the result is Equal to, OR, minus 1 to 3 before, OR,
plus 1 to 3 over (say 30), then diplay the result, otherwise
display nothing? Formula needs to be in row 3 and if the date are
different, as in B1, then the calculation would start over with
the new date in the next column.
Desired results are shown in row 3
My attempts have resulted in formula that is too long.
A B C D
1 Date 8/29/02 11/6/05 11/6/05
2 Draft 16 20 7
3 New 16 27

Would the row 1 dates always be ordered older to newer left to right? If so,
enter the following formula in B3.

B3:
=IF(COUNTIF($A$1:A$1,B$1)=0,SUMIF(B$1:$IV$1,B$1,B$ 2:$IV$2),"")

Fill B3 right as far as needed.

Luke wrote...
Yes that's right, date older to never.
....
I need it to add the numbers in row 2 that have the same date, Then if the
result is Equal to, say 30 days, OR, minus 1 to 3 before, OR, plus 1 to 3
over 30 days, then diplay the result, otherwise display nothing.

So you need to catch the range 27 to 33. Outside that range display
nothing ("").

For example, I have added columns to the original post.
Notice C2=20, C3="" then D2=7, D3=27 (3 days before total of 30 days), and
in E2=5, E3=32 but F2=2, F3="" (because the result would be 34, 1 day overthe
desired criteria)

But C1 and D1 differ. Are you changing specs, or is that a typo?

e.g. In D2 the numbers 7 thru 13 would have results in D3 (30-3=27, 30-2=28,
30-1=29, 30-0=30, 30+1=31, 30+2=32 and 30+3=33). Anything else would result
in "".
A B C D
1 Date 8/29/02 11/6/03 11/8/03
2 Draft 16 20 7
3 New 16 27

A E F G
1 Date 12/5/03 12/9/03 11/6/05
2 Draft 5 2 7
3 New 32
....

This is unclear. First you mention 'same date', but your examples show
different dates. You either need to fix your examples or your specs. If
the dates in the examples above are correct, then you're *not* matching
same dates.

Harlan,
So Sorry, I Should have said "Same Year". I bow in disgrace.
Luke

"Harlan Grove" wrote in message
oups.com...
Luke wrote...
Yes that's right, date older to never.
...
I need it to add the numbers in row 2 that have the same date, Then if the
result is Equal to, say 30 days, OR, minus 1 to 3 before, OR, plus 1 to 3
over 30 days, then diplay the result, otherwise display nothing.

So you need to catch the range 27 to 33. Outside that range display
nothing ("").

For example, I have added columns to the original post.
Notice C2=20, C3="" then D2=7, D3=27 (3 days before total of 30 days), and
in E2=5, E3=32 but F2=2, F3="" (because the result would be 34, 1 day
overthe
desired criteria)

But C1 and D1 differ. Are you changing specs, or is that a typo?

e.g. In D2 the numbers 7 thru 13 would have results in D3 (30-3=27,
30-2=28,
30-1=29, 30-0=30, 30+1=31, 30+2=32 and 30+3=33). Anything else would
result
in "".
A B C D
1 Date 8/29/02 11/6/03 11/8/03
2 Draft 16 20 7
3 New 16 27

A E F G
1 Date 12/5/03 12/9/03 11/6/05
2 Draft 5 2 7
3 New 32
...

This is unclear. First you mention 'same date', but your examples show
different dates. You either need to fix your examples or your specs. If
the dates in the examples above are correct, then you're *not* matching
same dates.

"Luke" wrote...
So Sorry, I Should have said "Same Year". I bow in disgrace.
....

Using years makes the formulas a bit more complicated.

B3:
=IF(AND(SUMPRODUCT(--(YEAR($A$1:A$1)=YEAR(B$1)))=0,
ABS(SUMPRODUCT(--(YEAR(B$1:$IV$1)=YEAR(B$1)),B$2:$IV$2)-30)<=3),
SUMPRODUCT(--(YEAR(B$1:$IV$1)=YEAR(B$1)),B$2:$IV$2),"")

Fill right as needed.