Setting up a sheets hierarchy/sorting

Bec1

So I have a sheet that I want to have organized by date, and then have totals based off the figures in that date.

This is what I have created so far, but not exactly sure how to have multiple inputs from users per payment on each date.

Insights??

Genevieve P.

Hi @Bec1

How do you determine what date/day the first screen capture is looking at?

I would suggest having a date field somewhere to reference in your Daily Report sheet. Then you can use this date field as a criteria for multiple SUMIFS formulas.

For example, if you have a Date Field in the Sheet Summary part of the sheet, you can display this date as Text in the Description column.

Then you can use the Date from the Summary Field as a date to search for in the Payment column of your source sheet (assuming that's a Date type of column).

=SUMIFS({Column to Sum}, {Payment Column}, [Day to Sum]#)

See: SUMIFS Function and Formulas: Reference Data from Other Sheets

You will need to create a different SUMIFS formula per row, where you're looking into different columns in the source sheet:

=SUMIFS({Cash Payment to Sum}, {Payment Column}, [Day to Sum]#)

=SUMIFS({Check Payment to Sum}, {Payment Column}, [Day to Sum]#)

However I see you have two Check columns. In this case you can add together two SUMIFS:

=SUMIFS({Patient Check to Sum}, {Payment Column}, [Day to Sum]#) + SUMIFS({Insurance Check to Sum}, {Payment Column}, [Day to Sum]#)

Cheers,

Genevieve

Genevieve P.

Hi @Bec1

You're almost there! So the references {in these} are Cross Sheet References, where you manually select a column from another sheet in a pop-up window so the formula knows what column to look at.

A SUMIFS structure in general is like this:

=SUMIFS({Column with numbers to Sum}, {Column with criteria to filter down}, "Criteria")

This means that in your case, the first {reference} would be the column that has information you want to SUM together, such as the "Cash Payment" column in your first screen capture. The second {reference} is then your date column to look for the Date that you've identified.

=SUMIFS({Cash Payment column}, {Date Column}, [Date]#)

Does that help clarify how to build this?

Genevieve P.

Hi @Bec1

You're very close! You're just missing the commas between the ranges:

=SUMIFS({Range 1}, {Range 2}, "Criteria")

You'll also need the # symbol after your Sheet Summary [reference] so it knows it's in the Sheet Summary portion of your sheet:

=SUMIFS({Range 1}, {Range 2}, [Reference]#)

Bec1

I should add that I already receive the raw data from a form submission into another sheet.

Genevieve P.

Hi @Bec1

How do you determine what date/day the first screen capture is looking at?

I would suggest having a date field somewhere to reference in your Daily Report sheet. Then you can use this date field as a criteria for multiple SUMIFS formulas.

For example, if you have a Date Field in the Sheet Summary part of the sheet, you can display this date as Text in the Description column.

Then you can use the Date from the Summary Field as a date to search for in the Payment column of your source sheet (assuming that's a Date type of column).

=SUMIFS({Column to Sum}, {Payment Column}, [Day to Sum]#)

See: SUMIFS Function and Formulas: Reference Data from Other Sheets

You will need to create a different SUMIFS formula per row, where you're looking into different columns in the source sheet:

=SUMIFS({Cash Payment to Sum}, {Payment Column}, [Day to Sum]#)

=SUMIFS({Check Payment to Sum}, {Payment Column}, [Day to Sum]#)

However I see you have two Check columns. In this case you can add together two SUMIFS:

=SUMIFS({Patient Check to Sum}, {Payment Column}, [Day to Sum]#) + SUMIFS({Insurance Check to Sum}, {Payment Column}, [Day to Sum]#)

Cheers,

Genevieve

Bec1

Thanks so much! This is super insightful.

1) How do you determine what date/day the first screen capture is looking at?

I haven't figured out how. I'm really a new user and haven't gotten it all down yet. :(

If I am understanding correctly..I create the summary from the sheet that is receiving the form submission right? I added the first summary using the date. It makes sense.

Still struggling with the row formulas. I keep getting an invalid reference....

Is Cash payments to sum another summary I am supposed tp reference?

Genevieve P.

Hi @Bec1

You're almost there! So the references {in these} are Cross Sheet References, where you manually select a column from another sheet in a pop-up window so the formula knows what column to look at.

A SUMIFS structure in general is like this:

=SUMIFS({Column with numbers to Sum}, {Column with criteria to filter down}, "Criteria")

This means that in your case, the first {reference} would be the column that has information you want to SUM together, such as the "Cash Payment" column in your first screen capture. The second {reference} is then your date column to look for the Date that you've identified.

=SUMIFS({Cash Payment column}, {Date Column}, [Date]#)

Does that help clarify how to build this?

Bec1

Grrr....I feel like I'm getting closer, but can't quite get this formula right.

Bec1

These are the references..

Genevieve P.

Hi @Bec1

You're very close! You're just missing the commas between the ranges:

=SUMIFS({Range 1}, {Range 2}, "Criteria")

You'll also need the # symbol after your Sheet Summary [reference] so it knows it's in the Sheet Summary portion of your sheet:

=SUMIFS({Range 1}, {Range 2}, [Reference]#)

Bec1

AHHHH!!!

I missed a comma and the # sign! Just goes to show how the details matter.

Thank you soooo much for all the help!!

Genevieve P.

Haha no problem at all! Yes, syntax is very specific when it comes to formulas. Well-done! 🙂