2nd attempt ~ Faster/Shorter formula
 

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

2nd attempt ~ Faster/Shorter formula
 

"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.

2nd attempt ~ Faster/Shorter formula
 

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.

2nd attempt ~ Faster/Shorter formula
 

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.

2nd attempt ~ Faster/Shorter formula
 

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.

2nd attempt ~ Faster/Shorter formula
 

"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.

2nd attempt ~ Faster/Shorter formula
 

Yes, a bit complicated. Formula in B3 (fill right) returns #VALUE! If I
jockey the formula around a bit and put it in C3 (fill right) I get all
blanks in row 3.
I will keep after it. Please let me know if you see something that needs
corrected. I'll do the same.


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

2nd attempt ~ Faster/Shorter formula
 

I'm stumped

"Luke" wrote in message
...
Yes, a bit complicated. Formula in B3 (fill right) returns #VALUE! If I
jockey the formula around a bit and put it in C3 (fill right) I get all
blanks in row 3.
I will keep after it. Please let me know if you see something that needs
corrected. I'll do the same.


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

2nd attempt ~ Faster/Shorter formula
 

Im stumped but I think I'll try using the first formula and manipulating
that somehow.

"Luke" wrote in message
...
Yes, a bit complicated. Formula in B3 (fill right) returns #VALUE! If I
jockey the formula around a bit and put it in C3 (fill right) I get all
blanks in row 3.
I will keep after it. Please let me know if you see something that needs
corrected. I'll do the same.


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

2nd attempt ~ Faster/Shorter formula
 

Luke wrote...
Yes, a bit complicated. Formula in B3 (fill right) returns #VALUE! If I
jockey the formula around a bit and put it in C3 (fill right) I get all
blanks in row 3.
....

My fault. Didn't test. The col B formula can't be the same as the col C
and further right formulas.

B3:
=IF(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),"")

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

2nd attempt ~ Faster/Shorter formula
 

"Harlan Grove" wrote:

My fault. Didn't test. The col B formula can't be the same as the col C
and further right formulas.

I see this as the column B3 formula to go into B3 and stay put. The Column
C3 formula to go into C3 and can be filled right as needed.
If that is correct I get blank cells throughout. I tried some other
variations with no luck.
However, I am on my work pc (different excel version) at this moment so I
will give it a go on my pc at home in a couple hours.

B3:
=IF(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),"")

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

2nd attempt ~ Faster/Shorter formula
 

"Harlan Grove" wrote in message
oups.com...
My fault. Didn't test. The col B formula can't be the same as the col C
and further right formulas.

Curious enough, if I put, in D2, either of the numbers 0 to 6 then C3
displays 27 to 33 respectively. This is very close to the goal but because
C2+D2=27 (30-3) then D3 should display 27 rather than C3, (i.e. To get
results in D3, D2 can = 7 to13, therefore D3 would = 27 to 33 respectively).

What I see happening, C3 is adding C2+D2 and if the sum is <20 or 27 then
results are "", but if the results are =20 and <=26 then I see reults.
Again however in C3 rather than the desired D3 (intended =27 and <=33)

I dare to point out that if by chance D2 was a 1 (rather than a 7) would the
formula then sum C2+D2+E2 to see if its sum is the intended =27 and <=33,
if not, then sum C2+D2+E2+F2 and so on (keep in mind the year) and
displaying the results in the final cell i.e F2.

In this case a revelation takes place (the following table changed for the
sake of argument and to correct an issue I didn't catch earlier). I
appologize, I got so wrapped up in this that I didn't make sure date (days)
were consistant with the count in row 2, and, B3 should have NEVER equalled
16.

A B C D
1 Date 8/29/02 11/6/03 11/8/03
2 Draft 16 20 2
3 New

A E F G
1 Date 11/13/03 11/18/03 11/25/05
2 Draft 5 5 7
3 New 26 31


ORIGINAL TABLE:
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




B3:
=IF(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),"")

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