How to sum values in a column that are greater than 1,500 but only sum the occurrences after the first 12?

spinitback

New Member
Joined
Aug 10, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi all. I'm using Excel for M365

I have a column of numbers
1597089807514.png



In this column I have 17 occurrences of a number greater than equal to 1500.
For the first 12 occurrences of a value greater than equal to 1500, I don't want to do anything.
For all other occurrences in that column (in the above example, 5 occurrences) with a value greater than equal to 1,500, I want to automatically calculate the sum of those occurrences.
ie for this column: 1500+3000+1500+3000+1500 = 10500

I'm hoping the forum can help, thanks!
-Spin
 

Attachments

  • 1597089766996.png
    1597089766996.png
    5.4 KB · Views: 6
How about
+Fluff New.xlsm
ABC
1
214787229
31741
41657
51852
61739
71405
81585
91717
101536
111967
121828
131606
141991
151558
161414
171766
181813
191749
201901
211459
Data
Cell Formulas
RangeFormula
C2C2=SUM(INDEX(FILTER(A2:A21,A2:A21>=1500),SEQUENCE(COUNTIF(A2:A21,">=1500")-12,,13)))
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
How about
+Fluff New.xlsm
ABC
1
214787229
31741
41657
51852
61739
71405
81585
91717
101536
111967
121828
131606
141991
151558
161414
171766
181813
191749
201901
211459
Data
Cell Formulas
RangeFormula
C2C2=SUM(INDEX(FILTER(A2:A21,A2:A21>=1500),SEQUENCE(COUNTIF(A2:A21,">=1500")-12,,13)))
Thanks for the help, this seemed to work on some columns but not on others. Here's a larger excerpt of the table. Maybe it will help
1597095055427.png
 
Upvote 0
How about
+Fluff New.xlsm
ABC
1
214787229
31741
41657
51852
61739
71405
81585
91717
101536
111967
121828
131606
141991
151558
161414
171766
181813
191749
201901
211459
Data
Cell Formulas
RangeFormula
C2C2=SUM(INDEX(FILTER(A2:A21,A2:A21>=1500),SEQUENCE(COUNTIF(A2:A21,">=1500")-12,,13)))
Fluff, thanks so much. It didn't work as expected as the Filter was bringing in the blank cells as well. Once I discovered how to restrict the filter array to just those values greater than 1500, it worked!
here is the formula that works:
=IF(R55>12,SUM(INDEX(FILTER(R4:R49,(R4:R49<>"")*(R4:R49>=1500)),SEQUENCE(COUNTIF(R4:R49,">=1500")-12,,13))),"")
 
Upvote 0
Glad you sorted it & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top