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

Pupishi

New Member
Joined
Oct 27, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
    Screenshot.png
    27.8 KB · Views: 8

Some videos you may like

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
Joined
Dec 8, 2006
Messages
2,115
Office Version
  1. 2010
Try
Excel Formula:
=SUM($C$4:index($C$4:$C$28,MATCH($F$2,$B$4:$B$28,0)))
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,165
Office Version
  1. 365
Platform
  1. Windows
Another option
Excel Formula:
=SUMIFS(C4:C33,B4:B33,"<="&F2)
 

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,115
Office Version
  1. 2010
For some unknown reason I never liked SUMIF, COUNTIF, etc.. Just stayed a fan of SUMPRODUCT :)
 

Pupishi

New Member
Joined
Oct 27, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,165
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,098
Messages
5,545,951
Members
410,713
Latest member
TaremyLunsil
Top