Setting up a sheets hierarchy/sorting

Options

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??



Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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]#)

Answers

  • Bec1
    Bec1 ✭✭✭
    Options

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



  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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
    Bec1 ✭✭✭
    Options

    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.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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
    Bec1 ✭✭✭
    Options

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


  • Bec1
    Bec1 ✭✭✭
    Options

    These are the references..


  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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
    Bec1 ✭✭✭
    Options

    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.
    Genevieve P. Employee Admin
    Options

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