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!

 

[James006]

Hello,

There are two separate questions in your initial message ...

First of all, have you managed to adapt Barry's Array Formula to your situation:

=SUM(IF(MOD(ROW($H$6:$H$3239)-ROW($H$6),41)=0,IF(ISNUMBER($H$6:$H$3239),$H$6:$H$3239)))

 

[Aladin Akyurek]

Are you wanting to count the occurrences of, e.g., 2 or sum them?

In order to count, in AF9 control+shift+enter, not just enter, and copy across:

=SUM(IF(MOD(ROW($H$6:$H$3239)-ROW($H$6),41)=0,IF(ISNUMBER($H$6:$H$3239),IF($H$6:$H$3239=COLUMNS($AF$9:AF9),1))))

In order to sum, in AF9 control+shift+enter, not just enter, and copy across:

=SUM(IF(MOD(ROW($H$6:$H$3239)-ROW($H$6),41)=0,IF(ISNUMBER($H$6:$H$3239),IF($H$6:$H$3239=COLUMNS($AF$9:AF9),$AF$9:AF9))))

 

[ab1997]

I was not able to adapt Barry's array formula to my situation.

 

[James006]

Have you tried the two Array Formulas proposed by Aladin in post #3... and/or the one in post #2 ...???

 

[ab1997]

Thank you for understanding, Aladin. I wasn't sure how to articulate what I was trying to say. Yes, I want to count the occurrences of each number that appears in that range. However, I tried your formula and it did not work for me. The result was "0" when at least one of the variable cells contained "1"

 

[ab1997]

I tried your formula as well, James. result was "0" when at least one variable cell contained "1".

 

[Aladin Akyurek]

Thank you for understanding, Aladin. I wasn't sure how to articulate what I was trying to say. Yes, I want to count the occurrences of each number that appears in that range. However, I tried your formula and it did not work for me. The result was "0" when at least one of the variable cells contained "1"

Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key. If done properly, Excel itself puts a pair of { and } around the formula in recognition.

COUNT:

In AF9 enter and copy across:

=SUM(IF(MOD(ROW($H$6:$H$3239)-ROW($H$6),41)=0,IF(ISNUMBER($H$6:$H$3239),IF($H$6:$H$3239=COLUMNS($AF$9:AF9),1))))

SUM:

=SUM(IF(MOD(ROW($H$6:$H$3239)-ROW($H$6),41)=0,IF(ISNUMBER($H$6:$H$3239),IF($H$6:$H$3239=COLUMNS($AF$9:AF9),$H$6:$H$3239))))

 

[ab1997]

Thanks guys, i found this formula online and I was able to adapt it for my situation.
<code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">=SUMPRODUCT((MOD(ROW(A2:A1000)-ROW(A2),5)=0)+0,ISNUMBER(SEARCH("text",A2:A1000))+0)</code>
Thanks so much for your help though!

 

[Aladin Akyurek]

Thanks guys, i found this formula online and I was able to adapt it for my situation.
<code style='margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;'>=SUMPRODUCT((MOD(ROW(A2:A1000)-ROW(A2),5)=0)+0,ISNUMBER(SEARCH("text",A2:A1000))+0)</code>
Thanks so much for your help though!

Looks familiar...

However, what happened to the 1 to 16 and AF9 stories?