# How to sum a range based on a condition using SUM(), OFFSET(), MATCH(), and COUNTIF()

#### Pupishi

##### New Member
Hello,

I am trying to sum up a range based on a condition, using the functions: SUM(), OFFSET(), MATCH(), and COUNT().

To take the figures in the screenshot as an example, the formula is retrieving 35 as the sum of 24 and 25 (numbers that are bigger than the condition, 23).
However, what I would like to see is the sum of numbers from the range 1 to 23 (instead of 24 to 25) in which the formula would retrieve a total of 136.
Would there be a way to modify the below formula to get the amount for numbers that are less than or equal to the condition?
I tried to simply change ">" to "<" but it didn't work.

=SUM(OFFSET(C4,MATCH(F2,B4:B28,0),0,COUNTIF(B4:B28,">"&F2)))

Thank you very much for your kind assistance.

#### Attachments

• Screenshot.png
27.8 KB · Views: 8

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### arthurbr

##### Well-known Member
Try
Excel Formula:
``=SUM(\$C\$4:index(\$C\$4:\$C\$28,MATCH(\$F\$2,\$B\$4:\$B\$28,0)))``

#### Fluff

##### MrExcel MVP, Moderator
Another option
Excel Formula:
``=SUMIFS(C4:C33,B4:B33,"<="&F2)``

#### arthurbr

##### Well-known Member
For some unknown reason I never liked SUMIF, COUNTIF, etc.. Just stayed a fan of SUMPRODUCT

#### Pupishi

##### New Member

Try
Excel Formula:
``=SUM(\$C\$4:index(\$C\$4:\$C\$28,MATCH(\$F\$2,\$B\$4:\$B\$28,0)))``
Thank you! This formula worked perfectly.

#### Pupishi

##### New Member
Another option
Excel Formula:
``=SUMIFS(C4:C33,B4:B33,"<="&F2)``
Thank you very much! This formula also worked

Most welcome

#### Fluff

##### MrExcel MVP, Moderator
Glad to help & thanks for the feedback.

Replies
2
Views
68
Replies
9
Views
119
Replies
4
Views
122
Replies
2
Views
169
Replies
10
Views
264