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

[Pupishi]

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.

 

[arthurbr]

Try

=SUM($C$4:index($C$4:$C$28,MATCH($F$2,$B$4:$B$28,0)))

 

[Fluff]

Another option

=SUMIFS(C4:C33,B4:B33,"<="&F2)

 

[arthurbr]

For some unknown reason I never liked SUMIF, COUNTIF, etc.. Just stayed a fan of SUMPRODUCT

 

[Pupishi]

Try

=SUM($C$4:index($C$4:$C$28,MATCH($F$2,$B$4:$B$28,0)))

Thank you! This formula worked perfectly.

 

[Pupishi]

Another option

=SUMIFS(C4:C33,B4:B33,"<="&F2)

Thank you very much! This formula also worked

 

[arthurbr]

Most welcome

 

[Fluff]

Glad to help & thanks for the feedback.