Sum Every nth Cell -Ignoring Errors in Range

[ab1997]

This has been asked before, answered and solved here:
https://www.mrexcel.com/forum/excel-questions/503738-sum-every-other-cell-ignoring-errors-range.html

The reason I'm revisiting it, is because I don't understand enough about how these formulas work. The solution given to this user worked for him, but I was unable to make it work for me. I thought I would see how lucky I was and hope one of you would tailor a solution for my specific issue as well.

---

QUESTION:
I have a single column which contains a number every 41 rows. Currently, each of the desired cells contain a #VALUE! error because they depend on another cell's contents to be populated. I want a formula that ignores those errors and counts the quantity of each possible value within that range. There are only 16 possible values (1 through 16), so for instance, any time it sees a "1", I want it to add that "1" to a separate column labeled "1". if there are 8 "1's" found, I want the output to say "8" and so on.

The range in question is H6:H3245
Every 41 rows, the value I want appears (as #VALUE! until its populated)
I want the outputs per value found (1 thru 16) to appear at AF9:AU9 (individual cells for all 16 possible sums)

Any help at all would be very appreciated!

 

[ab1997]

I used the formula that worked for me in each of those cells (AF9:AU9) except I change the "text" value to the number I wanted in each cell.

 

[Aladin Akyurek]

I used the formula that worked for me in each of those cells (AF9:AU9) except I change the "text" value to the number I wanted in each cell.

Given:

[TABLE="class: grid, width: 64"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11[/TD]
[/TR]
</tbody>[/TABLE]


=SUMPRODUCT((MOD(ROW(A2:A1000)-ROW(A2),5)=0)+0,ISNUMBER(SEARCH(1,A2:A1000))+0)

will return a count of 2 for 1 as search item.

Either...

{=SUM(IF(MOD(ROW($A$2:$A$1000)-ROW($A$2),5)=0,IF(ISNUMBER($A$2:$A$1000),IF($A$2:$A$1000=COLUMNS($AF$9:AF9),1))))}

Or, if you insist on a SumProduct formula...

=SUMPRODUCT((MOD(ROW($A$2:$A$1000)-ROW($A$2),5)=0)+0,ISNUMBER(MATCH($A$2:$A$1000,CHOOSE({1},COLUMNS($AF$9:AF9)),0))+0)

will return a count of 1 for 1.