# Minimum 3 Cell Sum

#### lholmes

##### Board Regular
I have 2 columns of data, I would like to be able to tell when a minimum of three consecutive cells in col a sum to less 21s, then report that sum in the same row in col c.
Any ideas anyone??
Sample data below

RECOVERY SPRINTS
42 1
61 6
18 1
25 7
3 1
5 2
9 1
31 2
5 6
24 1
1 3
26 4
5 1
2 1
17 2
63 3

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Jonmo1

##### MrExcel MVP
Assuming row 1 is headers, data starts in row 2

in C 4 put formula

=IF(SUM(A2:A4)<21,"Less than 21","")
Fill down

Hope this helps...
Personal.xls
ABCD
1RECOVERYSPRINTSFormula
2421
3616
4181
5257
631
752
891Lessthan21
9312
1056
11241
1213
13264
1451
1521
16172
17633
Sheet1

#### Jonmo1

##### MrExcel MVP
Oh wait, you wanted to show that sum if it was less than 21, I just put in a text flag...

chagne the formula to

=IF(SUM(A2:A4)<21,Sum(A2:A4),"")

#### lholmes

##### Board Regular
You fell into the same trap as I did, I have this already, but wanted to be able to min of 3 cells, so might be more. Couldn't figure that one out.

#### facethegod

##### Well-known Member
You fell into the same trap as I did, I have this already, but wanted to be able to min of 3 cells, so might be more. Couldn't figure that one out.

If your data is in A1:A16 try

Code:
``=IF(A1>20,"",IF(LOOKUP(1,1/(SUBTOTAL(9,OFFSET(A1,,,ROW(A1:\$A\$16)-ROW(A1)+1,1))<21),ROW(A1:\$A\$16)-ROW(A1)+1)>=3,SUM(A1:INDEX(A1:\$A\$16,LOOKUP(1,1/(SUBTOTAL(9,OFFSET(A1,,,ROW(A1:\$A\$16)-ROW(A1)+1,1))<21),ROW(A1:\$A\$16)-ROW(A1)+1))),""))``

#### Domenic

##### MrExcel MVP
Assuming that the data does not contain negative numbers, maybe...

C1, copied down:

=IF(SUM(A1:A3)>=21,"",LOOKUP(21,SUBTOTAL(9,OFFSET(A1:\$A\$16,,,ROW(A1:\$A\$16)-ROW(A1)+3))))

Hope this helps!

#### Domenic

##### MrExcel MVP
Assuming that the data does not contain negative numbers, maybe...

C1, copied down:

=IF(SUM(A1:A3)>=21,"",LOOKUP(21,SUBTOTAL(9,OFFSET(A1:\$A\$16,,,ROW(A1:\$A\$16)-ROW(A1)+3))))

Hope this helps!

Actually, a couple of changes need to be made...

1) Change

+3

to

+1

2) If the actual data does not contain decimal numbers, as per sample data, change...

21

to

20

Otherwise, change...

21

to

20.9999999999999

Hope this helps!

Hope this helps!

#### lholmes

##### Board Regular
Thanks for the help guys

Replies
0
Views
62
Replies
3
Views
29
Replies
12
Views
189
Replies
5
Views
95
Replies
3
Views
78

1,191,499
Messages
5,986,916
Members
440,064
Latest member
Pluong91

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back