Reference the top row everytime when new Form data in inputed

vaughnP

Hello. I am trying to reference the top row's information everytime a new entry/data is inputed into the sheet.

For context, when new data is captured in the form and input into the sheet, I want to be able to use a 'Countif' function to count all the '1s' (yeses) in the top row in the Sheet Summary.

Here is the formula that I'm using that is not working:

=COUNTIF([Are the evaporator coils clean? (1=yes 0=no)]$1:[Electrical components functional? (1=yes 0=no)]$1, "1") / (21)

When a new entry from the form is captured and input into the sheet, it changes the '$1' to '$2'.

Is there a way to always reference the first row's data when new data is input into the sheet...?!

Paul Newcome

Try inserting an auto-number type column with no special formatting. Then you can use:

=COUNTIFS([Are the evaporator coils clean? (1=yes 0=no)]:[Electrical components functional? (1=yes 0=no)], "1", [Auto-Number]:[Auto-Number], @cell = MAX([Auto-Number]@row)

vaughnP

I have an autonumber column called 'Inspection#'. I entered the 'Inspection#' in where 'Auto-Number' was listed. I'm getting the #UNPARSEABLE error message. Below is fx I'm using:

=COUNTIFS([Are the evaporator coils clean? (1=yes 0=no)]:[Electrical components functional? (1=yes 0=no)], "1", [Inspection#]:[Inspection#], @cell = MAX([Inspection#]@row)

Paul Newcome

Double check the columns names to ensure they are spelled correctly including spacing.

Are you able to provide a screenshot of the formula actually in the sheet itself?

Kelly Moore

Hey Y'all

I was doing something similar a short while back and ran into the same error. I found it was caused by the multiple column range. I never figured out why - I was in a hurry for a solution. I added a helper column and did my COUNTIFS across the row as a column formula. =COUNTIFS([Column1]@row:[Column at End of range]@row, 1) *remember you don't typically put numbers in quotes.

Then, my real formula became (using your equivalent to [Row ID]

=VALUE(JOIN(COLLECT([helper countifs column]:[helper countifs column], [Inspection#]:[Inspection#], @cell = MAX([Inspection#]:[Inspection#]))))

Hope it helps - or at least helps to produce a 'prettier' solution.

Kelly

vaughnP

Kelly-

Can you elaborate on the 'helper' column..? I have an 'Inspection# column that is autogenerates the RowID. Is this the 'helper' column in which you are referring..? I've also included a quick snapshot of the first few attributes in the sheet for reference. This fxn is for the Sheet Summary data

Kelly Moore

Hey

The helper column is collecting your COUNTIFS for the row. You can shove this column to the right and hide it if you prefer

=COUNTIFS([Column1]@row:[Column at End of range]@row, 1)

Then use this helper column to collect the data you want.

=VALUE(JOIN(COLLECT([helper countifs column]:[helper countifs column], [Inspection#]:[Inspection#], @cell = MAX([Inspection#]:[Inspection#]))))

I was hoping someone would have a 'prettier' solution that didn't need the helper column but I couldn't get it to work without one. 😉 But 'working' is always a good thing.

Kelly



I was surprised, just like you, when I tried an approach almost identical to yours and it wouldn't work - at least in the time frame I had to get it working.