Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How to properly use ARRAYFORMULA in Google Sheets to calculate Form Responses

I have responses in column D that are in HH:MM:SS format that I need to convert to seconds as the responses come in. I understand that doing an array in another column in Sheets is the proper solution, however, I can’t seem to figure out the syntax. Here is the formula not in an array: "=IF(D2="","",(sum(hour(D2)*3600,minute(D2)*60,second(D2))))". Can somebody help with how to make this formula into an ArrayFormula?

I tried a few different versions but they were not even close I imagine.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

You may try this; the formula goes into row_1 (e.g. X1)

=vstack(
        "Header",
        map(indirect("D2:D"),lambda(Σ,if(Σ="",,sum(hour(Σ)*3600,minute(Σ)*60,second(Σ)))))
        )
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading