Selective Sum of every Nth row given interval, range, and check variable

[MallMan]

Howdy, not new to coding but new to VBA.
I have made a sample version of the data I'm working with below.

Code	Label 1	Label 2	Label 3	Label 1	Label 2	Label 3	Label 1	Label 2	Label 3
20500			1			6			1
20000			2			5			2
20000			3			4			3
20500			4			3			4
20500			5			2			5
20000			6			1			6

My goal is to create a function that will sum all values from the label 3 columns, given that they have the specified code. My current thought process for this is to take the Range (User Defined selection of cells. In this case a box from the top 20500 to the bottom right 6), the step (every 3 in this case), and the check value/ variable (20500 or 20000 in this case) and try to complete this using that.

My thoughts were to have a grand total that is added to as rows/columns are indexed through in a for loop but I haven't been able to make this work.

How should I go about this?

Thank you in advance!

 

[Anthony47]

Hummm...

My goal is to create a function that will sum all values from the label 3 columns, given that they have the specified code.

Maybe I didn't get your question, but such a function is already available in Excel and is named SUMIF; so, assuming "Code" is in column A and Label3 is D, you get the result using

=SUMIF(A2:A1000,20500,D2:D1000)

If your question was another one, then try explaining it again

 

[MallMan]

This partially works but my issue is that I have a potentially infinite number of label 3 columns. I need to check and sum columns D, G, J, M, and so on to take the numbers from all of them.

So if a function were to work properly, searching for 20500, it would return (1+6+1+4+3+4+5+2+5) 31.

Thank you for your help!

 

[Anthony47]

Try one of these two:
1-Short

=SUM(IFERROR((B1:M11)*(A1:A11=20500)*(B1:M1="Label 3"),0))

2-Longer, but maybe easier to adapt

=LET(BigArr,A1:M11,myCode,20500,myHead,"Label 3",Code,INDEX(BigArr,0,1),Heads,INDEX(BigArr,1,0),SUM(IFERROR(BigArr*(Code=myCode)*(Heads=myHead),0)))

In this second formula the parametres are at the beginning of the formula

 

[MallMan]

Try one of these two:
1-Short

=SUM(IFERROR((B1:M11)*(A1:A11=20500)*(B1:M1="Label 3"),0))

2-Longer, but maybe easier to adapt

=LET(BigArr,A1:M11,myCode,20500,myHead,"Label 3",Code,INDEX(BigArr,0,1),Heads,INDEX(BigArr,1,0),SUM(IFERROR(BigArr*(Code=myCode)*(Heads=myHead),0)))

In this second formula the parametres are at the beginning of the formula

The first version worked for me, thank you so much for your help!

 

[Anthony47]

Thank you for the feedback.
The second formula do the same thing, but in a syntax available in MS 365 that theoretically is easier to mantain, as the "parametres" are outside the final computatation: SUM(IFERROR(BigArr*(Code=myCode)*(Heads=myHead),0))