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

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
Joined
Dec 8, 2006
Messages
2,114
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,076
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,114
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,076
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,928
Messages
5,545,080
Members
410,652
Latest member
Zot
Top