# Selective Sum equation

#### kckrumes

##### New Member
How do I sum based on a condition?

If B2 is equal to 21, then I want to sum(a1:a21). If B2 is equal to 43, then I want to sum(a1:a43). If B2 is qual to 67, then sum(a1:a67). And so on...

It seems so simple. I figure I am overlooking something obvious but don't know what. I would appreciate any input.

kc

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### attc

##### Board Regular
the ever useful INDIRECT() could be used:

=SUM(INDIRECT("A1:A"&B2))

there are probably lots of other good ways I haven't thought of, but that will work :wink:

##### MrExcel MVP
here's another:

=SUM(OFFSET(A1,0,0,B2,1))

#### kckrumes

##### New Member
Thanks! It worked great.

kc

##### MrExcel MVP
Also, non-volatile,

=SUM(A1:INDEX(A:A,B1,1))

Replies
12
Views
342
Replies
7
Views
446
Replies
3
Views
396
Replies
3
Views
262
Replies
6
Views
139

1,190,862
Messages
5,983,272
Members
439,836
Latest member
BuckyBoyRx

### 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