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

[spinitback]

Hi all. I'm using Excel for M365

I have a column of numbers

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

 

[spinitback]

Forgot to mention - I need help with a formula to do this as I have a large table spanning several years.

Thanks
S

 

[sandy666]

don't you think Power Query would be better especially with large data?

Raw		Table1
1500		10500
1500
500
1500
3000
500
1500
3000
500
1500
1500
500
3000
1500
500
1500
3000
500
1500
3000
1500
500
3000
1500

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Raw", Int64.Type}}),
    Filter = Table.SelectRows(Type, each [Raw] >= 1500),
    Top12 = Table.Skip(Filter,12),
    Sum = List.Sum(Top12[Raw])
in
    Sum

 

[spinitback]

thanks sandy, I appreciate the help. Is there a way to turn your power query code into an excel formula?

 

[sandy666]

thanks sandy, I appreciate the help. Is there a way to turn your power query code into an excel formula?

maybe but I don't know
but it should be easy
- filter >=1500
- sumif (maybe)
- sum the rest

but i am sure soon any formula master will give a solution

 

[lrobbo314]

Not sure how you would do this without using a helper column. So here is a way to do it either using a helper column or using the custom function included below.

New Document (2).xlsx
	A	B	C	D
1	1500	1	10500	Formula
2	1500	2	10500	VBA Custom Function
3	500	2
4	1500	3
5	3000	4
6	500	4
7	1500	5
8	3000	6
9	500	6
10	1500	7
11	1500	8
12	500	8
13	3000	9
14	1500	10
15	500	10
16	1500	11
17	3000	12
18	500	12
19	1500	13
20	3000	14
21	1500	15
22	500	15
23	3000	16
24	1500	17
Sheet9

Cell Formulas
Range	Formula
C1	C1	=SUMIFS($A$1:$A$24,$A$1:$A$24,">=1500",$B$1:$B$24,">12")
C2	C2	=xSum(A1:A24,1500,12)
B1:B24	B1	=COUNTIF($A$1:A1,">=1500")

Function xSum(r As Range, Limit As Integer, IDX As Integer) As Integer
Dim AR() As Variant: AR = r.Value2
Dim CNT As Integer: CNT = 0

For Each a In AR
    If a >= Limit Then
        CNT = CNT + 1
        If CNT > IDX Then xSum = xSum + a
    End If
Next a
End Function

 

[spinitback]

Not sure how you would do this without using a helper column. So here is a way to do it either using a helper column or using the custom function included below.

New Document (2).xlsx
	A	B	C	D
1	1500	1	10500	Formula
2	1500	2	10500	VBA Custom Function
3	500	2
4	1500	3
5	3000	4
6	500	4
7	1500	5
8	3000	6
9	500	6
10	1500	7
11	1500	8
12	500	8
13	3000	9
14	1500	10
15	500	10
16	1500	11
17	3000	12
18	500	12
19	1500	13
20	3000	14
21	1500	15
22	500	15
23	3000	16
24	1500	17
Sheet9

Cell Formulas
Range	Formula
C1	C1	=SUMIFS($A$1:$A$24,$A$1:$A$24,">=1500",$B$1:$B$24,">12")
C2	C2	=xSum(A1:A24,1500,12)
B1:B24	B1	=COUNTIF($A$1:A1,">=1500")

Function xSum(r As Range, Limit As Integer, IDX As Integer) As Integer
Dim AR() As Variant: AR = r.Value2
Dim CNT As Integer: CNT = 0

For Each a In AR
    If a >= Limit Then
        CNT = CNT + 1
        If CNT > IDX Then xSum = xSum + a
    End If
Next a
End Function

Hey, thanks for the help! Is there a way to do the Excel formula without the need for the "B" column, ie just based on the A column? I have a column for each month for 4 years.

 

[CA_Punit]

Try This without countif helper

=IFERROR(SUM(INDEX($A$2:$A$25,AGGREGATE(15,6,1/(1/((A2:A25>=1500)*(ROW($A$2:$A$25)-ROW($A$2)+1))),ROW(INDIRECT("A13:A"&COUNTIF(A2:A25,">="&1500)))))),0)

 

[lrobbo314]

Nice @CA_Punit, looks better than the nightmare formula I came up with.

New Document (2).xlsx
	A	B	C
1	1500		10500
2	1500
3	500
4	1500
5	3000
6	500
7	1500
8	3000
9	500
10	1500
11	1500
12	500
13	3000
14	1500
15	500
16	1500
17	3000
18	500
19	1500
20	3000
21	1500
22	500
23	3000
24	1500
Sheet9

Cell Formulas
Range	Formula
C1	C1	=SUMIF(OFFSET(A1:A24,SMALL(IF(A1:A24>=1500,ROW(A1:A24)-ROW(A1)+1),13)-1,0,ROWS(A1:A24)-SMALL(IF(A1:A24>=1500,ROW(A1:A24)-ROW(A1)+1),13)+1),">=1500",OFFSET(A1:A24,SMALL(IF(A1:A24>=1500,ROW(A1:A24)-ROW(A1)+1),13)-1,0,ROWS(A1:A24)-SMALL(IF(A1:A24>=1500,ROW(A1:A24)-ROW(A1)+1),13)+1))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[spinitback]

Try This without countif helper

=IFERROR(SUM(INDEX($A$2:$A$25,AGGREGATE(15,6,1/(1/((A2:A25>=1500)*(ROW($A$2:$A$25)-ROW($A$2)+1))),ROW(INDIRECT("A13:A"&COUNTIF(A2:A25,">="&1500)))))),0)

Thanks for the help. What is the significance of the "A13:A" reference? Each column will be different, ie with difference values. Is this A13 only for this example?.