Formulas
In a form.
I want to add data from one sheet and popluate another sheet, the issue i am having is the form sheet receives new requests each day. Where and how do i change the formula below to capture all new data in the sheet. I though i would use the $ or @row, this only issue is i have a summary line at the top of my form
=SUMIF({Booking Form (Accommodation) Range 1}, ="Proj:2", {Booking Form (Accommodation) Range 1})
Best Answer

Hi @SharonR
In order to capture new data, you would need to reference the entire column (including the first summary line at the top). Would you be able to keep the summary line in a different place? Depending on the data, you may want to look into using Sheet Summary fields (see here for more information).
In regards to your formula, it looks like you have Range 1 inserted twice. Your first part of the statement should be the Range with the Criteria, then the Criteria. The last range at the end is the column you want to SUM. For example:
=SUMIF({Booking Form (Accommodation) Range 1}, "Proj:2", {Booking Form (Accommodation) Range 2})
This will SUM the data in Range 2, but only if that row's cell in range 1 is "Proj:2". These ranges are entire columns so they will automatically update when new information is filled in. (Click Here for more info on CrossSheet references)
If you're having trouble building this, it would be useful to see a screen capture of the source sheet, explaining what Ranges 1 and 2 are.
Thanks!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

Hi @SharonR
In order to capture new data, you would need to reference the entire column (including the first summary line at the top). Would you be able to keep the summary line in a different place? Depending on the data, you may want to look into using Sheet Summary fields (see here for more information).
In regards to your formula, it looks like you have Range 1 inserted twice. Your first part of the statement should be the Range with the Criteria, then the Criteria. The last range at the end is the column you want to SUM. For example:
=SUMIF({Booking Form (Accommodation) Range 1}, "Proj:2", {Booking Form (Accommodation) Range 2})
This will SUM the data in Range 2, but only if that row's cell in range 1 is "Proj:2". These ranges are entire columns so they will automatically update when new information is filled in. (Click Here for more info on CrossSheet references)
If you're having trouble building this, it would be useful to see a screen capture of the source sheet, explaining what Ranges 1 and 2 are.
Thanks!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
Check out the Formula Handbook template!