# 2nd attempt ~ Faster/Shorter formula

1. ## 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  Register To Reply

2. ## Re: 2nd attempt ~ Faster/Shorter formula

"Luke" <Luke@discussions.microsoft.com> 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.  Register To Reply

3. ## Re: 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.
Luke

"Harlan Grove" wrote:

> "Luke" <Luke@discussions.microsoft.com> 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.
>
>
>  Register To Reply

4. ## Re: 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.  Register To Reply

5. ## Re: 2nd attempt ~ Faster/Shorter formula

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

"Harlan Grove" <hrlngrv@aol.com> wrote in message
> 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.
>  Register To Reply

6. ## Re: 2nd attempt ~ Faster/Shorter formula

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

"Harlan Grove" <hrlngrv@aol.com> wrote in message
> 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.
>  Register To Reply

7. ## Re: 2nd attempt ~ Faster/Shorter formula

"Luke" <cen50951@centurytel.net> 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.  Register To Reply

8. ## Re: 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" <hrlngrv@aol.com> wrote in message
news:eVvbFvz5FHA.2576@TK2MSFTNGP09.phx.gbl...
> "Luke" <cen50951@centurytel.net> 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.
>  Register To Reply

9. ## Re: 2nd attempt ~ Faster/Shorter formula

I'm stumped

"Luke" <cen50951@centurytel.net> wrote in message
news:OEH0IX15FHA.2888@tk2msftngp13.phx.gbl...
> 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" <hrlngrv@aol.com> wrote in message
> news:eVvbFvz5FHA.2576@TK2MSFTNGP09.phx.gbl...
>> "Luke" <cen50951@centurytel.net> 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.
>>

>
>  Register To Reply

10. ## Re: 2nd attempt ~ Faster/Shorter formula

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

"Luke" <cen50951@centurytel.net> wrote in message
news:OEH0IX15FHA.2888@tk2msftngp13.phx.gbl...
> 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" <hrlngrv@aol.com> wrote in message
> news:eVvbFvz5FHA.2576@TK2MSFTNGP09.phx.gbl...
>> "Luke" <cen50951@centurytel.net> 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.
>>

>
>  Register To Reply

11. ## Re: 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),"")  Register To Reply

12. ## Re: 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),"")  Register To Reply

13. ## Re: 2nd attempt ~ Faster/Shorter formula

"Harlan Grove" <hrlngrv@aol.com> wrote in message
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),"")  Register To Reply