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 Cross-Sheet 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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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 Cross-Sheet 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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!