Sum only the last four entries

**Dave69rock** · 01-21-2009, 06:13 AM

I have a spreadsheet that i update weekly

What i need is a formula that will just sum the last four weeks

Example column A rows 1-52 will be updated each week in row 53 i just want the last four weeks to be summed.

Dave

**sweep** · 01-21-2009, 06:16 AM

Hi,

To sum the last four populated rows in column A,

=SUM(OFFSET(A1,COUNT(A1:A52)-1,0,-4,1))

**Dave69rock** · 01-21-2009, 06:35 AM

Thanks sweep

**DonkeyOte** · 01-21-2009, 07:17 AM

Sweep you might want to add some additional logic to your Row offset parameter to handle the fact that you may have less than 4 weeks of data at a given point in time.

Below is another approach which avoids use of OFFSET (volatile)

=SUM(INDEX(A1:A52,MAX(1,COUNT(A1:A52)-4),1):A52)

The use of MAX will ensure you will always get a valid answer.

**martindwilson** · 01-21-2009, 07:37 AM

=SUM(INDEX(A1:A52,MAX(1,COUNT(A1:A52)-3),1):A52)
else you'd get last 5 values not 4

**DonkeyOte** · 01-21-2009, 07:39 AM

correctamundo...
(it was in all honesty a typo)

**ashwinsindhu** · 10-28-2010, 02:19 PM

Hi could any help me on this formula.

=SUMPRODUCT((Date>=$B$5)*(Date<=$B$6)*(Productname=$B$7)*(Causingarea=A18)*Downtime)

Date Product PQVC Causing Area Down Time (Hrs)
10/20/10 WTS q MISC 1.25
10/25/10 QCT v MISC 1.25
10/30/10 QCT v SQ 3.5
10/30/10 WTS c LPT 1.75
i am getting #N/A i dont know why. if remove (Productname=$B$7) or )*(Causingarea=A18) i am getting the correct value, can any one help me

**NBVC** · 10-28-2010, 02:42 PM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.