COUNT formula not working on form submissions due to row numbers changing

Shane Beam

@JamesB Hey James, I am Having the same issue as Dawn but your solution does not seem to work for me. can you help me out?

JamesB

@Shane Beam

I would be happy to assist, if you can share with me your scenario, any formulas you have and screenshots with confidential data redacted, we will see what we can do.

Shane Beam

here is the formula:

=SUM($[Near Miss Numeric]$1:$[Near Miss Numeric]$30)

i just want it to add the first 30 rows and give me a total but when i use the form to submit data is changes the referenced cell numbers.

JamesB

@Shane Beam

When the form is submitted, is the new row added to the top?

Shane Beam

@JamesB Yes and that function is relatively critical to how the information is used.

JamesB

@Shane Beam There may be other ways to do this, but since you are statically only getting the first 30 rows of data, I have found that the INDEX formula will not change with a form submission. This will be a long formula, but easily repeatable. You can see below I have the first 3 rows, you can copy paste to get it to 30.

=SUM(INDEX([Near Miss Numeric]:[Near Miss Numeric], 1, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 2, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 3, 0))

Paul Newcome

@Shane Beam You can insert an auto-number column with no special formatting and leverage that with a SUMIFS to only grab the top 30 rows.

=SUMFS([Near Miss Numeric]:[Near Miss Numeric], Auto:Auto, @cell>= LARGE(Auto:Auto, 30))

Shane Beam

@Paul Newcome this one came back #unparseable

actually i added in The "I" in SUMIFS and it came back "invalid value"

Shane Beam

@JamesB i copied and pasted until i got to 30 and i got #unparseable her is what i put it:

=SUM(INDEX([Near Miss Numeric]:[Near Miss Numeric], 1, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 2, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 3, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 4, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 5, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 6, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 7, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 8, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 9, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 10, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 11, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 12, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 13, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 14, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 15, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 16, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 17, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 18, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 19, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 20, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 21, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 22, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 23, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 24, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 25, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 26, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 27, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 28, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 29, 0)) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 30, 0)) 

JamesB

@Shane Beam You are close, but the double parentheses at the end was just to close the SUM formula. You have it on everyone. See corrected below.

=SUM(INDEX([Near Miss Numeric]:[Near Miss Numeric], 1, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 2, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 3, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 4, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 5, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 6, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 7, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 8, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 9, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 10, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 11, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 12, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 13, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 14, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 15, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 16, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 17, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 18, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 19, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 20, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 21, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 22, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 23, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 24, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 25, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 26, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 27, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 28, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 29, 0) + INDEX([Near Miss Numeric]:[Near Miss Numeric], 30, 0)) 

Shane Beam

@JamesB Thanks James. this worked like a charm, you are a gentleman and a scholar! i tried to extrapolate it out too 100 lines and it cuts off the formula, i assume there is a character limit?

JamesB

@Shane Beam Not sure, there could be. you could just create a 1-50 in one cell and a 50-100 in another cell, then in the 3rd cell add them together.

JamesB

@Paul Newcome I looked at the autonumber option, and what I noticed was everytime a new form entry was submitted at the top row, it did not renumber the rows, it put the next logical number in the top row. so once it got past 30 rows, 31 would be at the top, meaning you would want to collect 31 then 1-29. and skip 30, because it it now outside the threshold. Am I missing something here?

Paul Newcome

@Shane Beam Good catch on that missing I. sorry about that. The error may be because there are not 30 entries yet. Try this adjustment:

=IFERROR(SUMIFS([Near Miss Numeric]:[Near Miss Numeric], Auto:Auto, @cell>= LARGE(Auto:Auto, 30)), SUM([Near Miss Numeric]:[Near Miss Numeric]))

@JamesB

=SUMIFS([Near Miss Numeric]:[Near Miss Numeric], Auto:Auto, @cell>= LARGE(Auto:Auto, 30))

The LARGE function would pull in the 30th largest number from the auto-number column. So if I have 30 rows, the LARGE function would output 1. Any row with a number greater than or equal to 1 would be included in the SUMIFS. If I then ad a 31st row, the 30th largest number is now 2. That means 2 - 31 will be included in the SUMIFS. I add another row and 3 becomes the 30th largest number so 3 - 32 gets pulled in, so on and so forth.

In the even there are not 30 rows and the LARGE function throws an error, we know we want to just sum all the rows since all would be within the 30 most recent. That's where the IFERROR comes in with the basic SUM in the adjustment mentioned above.

Shane Beam

@Paul Newcome that new one works but it appears to capture the whole sheet not the just the first 30 rows.