# 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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### arthurbr

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

• Pupishi

#### Fluff

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

• Pupishi

#### 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
66
Replies
9
Views
116
Replies
4
Views
121
Replies
2
Views
166
Replies
10
Views
259