Formulas

SharonR

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})

Genevieve P.

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

Genevieve P.

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