Summing the last 7 non-blank entries in a row of data
 

I have a continually growing row of data that I want to sum automatically.
An example is: I want to store the sum in cell A1, data is currently in cell
B1, C1, E1, F1, and H1. B1=2, C1=3, E1=1, F1=4, H1=2. I want the result in
A1 to = 12. The next day I add data to cells I1, J1, K1, and M1, where I1=2,
J1=3, K1=2, M1=5. After adding the new data, I want the result in A1 =
E1+F1+H1+I1+J1+K1+M1 = 19. I found the following equation in one of the
discussion threads for this scenario, but I noticed it doesn't add up
correctly. Is there a way to do do this? Any help would be appreciated!

Found equation: =SUM(OFFSET(B1,,COUNTA(B1:CO1),,-(MIN(COUNTA(B1:CO1),7))))

Summing the last 7 non-blank entries in a row of data
 

Hi!

There's a difference in the two segments:

The first set is of 7 cells (but not 7 numbers) and the next set you want
the last 7 numbers.

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=IF(COUNT(B1:IV1)<7,SUM(B1:IV1),SUM(IV1:INDEX(B1:I V1,LARGE(IF(B1:IV1<"",COLUMN(B1:IV1)-1),7))))

If there are less than 7 entries all will be summed, otherwise only the last
7 entries will be summed.

Biff

"not an excel guru" <not an excel wrote in
message ...
I have a continually growing row of data that I want to sum automatically.
An example is: I want to store the sum in cell A1, data is currently in
cell
B1, C1, E1, F1, and H1. B1=2, C1=3, E1=1, F1=4, H1=2. I want the result
in
A1 to = 12. The next day I add data to cells I1, J1, K1, and M1, where
I1=2,
J1=3, K1=2, M1=5. After adding the new data, I want the result in A1 =
E1+F1+H1+I1+J1+K1+M1 = 19. I found the following equation in one of the
discussion threads for this scenario, but I noticed it doesn't add up
correctly. Is there a way to do do this? Any help would be appreciated!

Found equation: =SUM(OFFSET(B1,,COUNTA(B1:CO1),,-(MIN(COUNTA(B1:CO1),7))))

Summing the last 7 non-blank entries in a row of data
 

Biff,

Sorry for the late reply, I was in a training class last week and didn't get
back too my problem. I tried it out and your equation worked to sum the last
5. I changed the last part from Column(B1:IV1)-1 to Column(B1:IV1)-3 and
that summed the last 7. I don't understand why that worked and yours summed
5 cells. Thank you so much for the help!!!!

"Biff" wrote:

Hi!

There's a difference in the two segments:

The first set is of 7 cells (but not 7 numbers) and the next set you want
the last 7 numbers.

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=IF(COUNT(B1:IV1)<7,SUM(B1:IV1),SUM(IV1:INDEX(B1:I V1,LARGE(IF(B1:IV1<"",COLUMN(B1:IV1)-1),7))))

If there are less than 7 entries all will be summed, otherwise only the last
7 entries will be summed.

Biff

"not an excel guru" <not an excel wrote in
message ...
I have a continually growing row of data that I want to sum automatically.
An example is: I want to store the sum in cell A1, data is currently in
cell
B1, C1, E1, F1, and H1. B1=2, C1=3, E1=1, F1=4, H1=2. I want the result
in
A1 to = 12. The next day I add data to cells I1, J1, K1, and M1, where
I1=2,
J1=3, K1=2, M1=5. After adding the new data, I want the result in A1 =
E1+F1+H1+I1+J1+K1+M1 = 19. I found the following equation in one of the
discussion threads for this scenario, but I noticed it doesn't add up
correctly. Is there a way to do do this? Any help would be appreciated!

Found equation: =SUM(OFFSET(B1,,COUNTA(B1:CO1),,-(MIN(COUNTA(B1:CO1),7))))