Complex lookup and report data wanted

Options

Good morning all. I have a complex procedure I want to try and accomplish.

I have 2 sheets currently created. 1 is for our main invoice for a job, that has a payment schedule and a checkbox colum if the scheduled item has been invoiced.

I have a second sheet that allows us to input all of the change orders, their amount and it totals them in a total column at the top, and that total column is then linked to the original invoices sheet. I have attached a screenshot of both.

What I want to achieve.

We have to report weekly to our supervisor what we have invoiced for the week, so they keep a running total.

I wanted to create a report that would look up todays date (Which we would use as each friday as the base date for counting back) and check this current week if we have invoiced anything.

My thought process is, if I create a metric sheet, and that metric has a formula that checks the invoice sheet.

It checks what todays date is, then checks the week from monday to friday, looks at the check box to see if the check box has been checked, which would indicate an invoice has been sent this week, then it would report how much that invoice was and sent out. It would also check if that date was greater than monday (so lets say we invoiced something 10 days ago) it would see that it has already been invoiced and it wouldn't display that date in the report.

I am pretty beginner level at these formulas and stuff, so let me know if you think there is a better way to achieve this?

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You should be able to use a SUMIFS.

    =SUMIFS({Amount}, {Checkbox}, @cell = 1, {Dates}, AND(IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

  • Bret Kennedy
    Options

    Thanks for this, I don't really understand the entirety of this formula, any chance there can be some more discussion on it? ie: What each value means/represents? I don't see anywhere that it is only looking for dates 'this week' ie: monday through Friday, and then if the checkbox is already checked to ignore the value.


    Thanks for your help so far ;)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    =SUMIFS({Amount}, {Checkbox}, @cell = 1, {Dates}, AND(IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))


    {Amount} --> The range you want to sum up

    {Checkbox} --> The range containing the checkbox column

    @cell = 1 --> Box (from previous range) is checked

    {Dates} --> The range containing the dates

    AND(IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())) --> Date criteria

    AND --> Used to combine multiple criteria for a single range into a single set

    IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) --> Compares the week number of the date to the current week number (IFERROR used to exclude cells within the range that are blank throwing an error.

    IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) --> Same as week number but for the year to avoid the possibility of a date being in the same week number but a different year being included in the calculation.

  • Bret Kennedy
    Options

    ok thanks Paul, so talking through this and the screenshots I provided,

    {Amount} - You say a range, but when inputting that into the formula for the sheet, where is the range? There are 4 different possible payments made.

    • Contract Deposit
    • 50% completion
    • Finished work
    • Net 30

    So how do I choose a range here?

    You have week numbers as part of your formula, that is something I don't understand, actually that whole section of year and weeks is hard for me to grasp.


    {Dates} = This is the range of dates, but again how am I to input that? The logic from other formulas I researched was something like

    check todays date, <= 5, if checkbox is checked and the date falls in that range, (- 4 days) what is the value in the payment cell?


    Also, is the sumifs formula we are using, supposed to add up all of the invoices? because in this particular part, where it is relating to just this job, there would never be more than 1 invoice in that specific week. I can see how that could be used later when I want to calculate this over multiple jobs, ie I invoiced 3 jobs this week, they are all part of different project folders etc.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    The ranges are created by using cross sheet references to the sheet containing the source data.


    Here is what you have in your original post:

    "It checks what todays date is, then checks the week from monday to friday, looks at the check box to see if the check box has been checked, which would indicate an invoice has been sent this week, then it would report how much that invoice was and sent out. It would also check if that date was greater than monday (so lets say we invoiced something 10 days ago) it would see that it has already been invoiced and it wouldn't display that date in the report."

    It checks what todays date is, then checks the week from monday to friday --> WEEKNUMBER and YEAR functions make sure that it is in the current week.

    looks at the check box to see if the check box has been checked, --> Checkbox has been checked (@cell = 1)

    It would also check if that date was greater than monday (so lets say we invoiced something 10 days ago) it would see that it has already been invoiced and it wouldn't display that date in the report." --> WEEKNUMBER and YEAR functions filter out anything that wasn't in the current week.


    If you wanted to also filter by a specific invoice number, then you would add another range/criteria set following the same syntax.

    =SUMIFS(range to sum, criteria range 1, criteria 1, criteria range 2, criteria 2, criteria range 3, criteria 3)

  • Bret Kennedy
    Bret Kennedy ✭✭
    edited 03/30/23
    Options

    ok, So I managed to get it to return a 0 value. This is what I have for my formula.

    =SUMIFS({Bakersfield Projects 2023 Range 4}, {Bakersfield Projects 2023 Range 4}, @cell = 1, {Bakersfield Projects 2023 Range 4}, AND(IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))


    I input the date for the deposit as 3/27/23, and the 50% compmletion as 3/29/23 with both checkboxes checked.

    Screenshot attached. What am I missing for the figures to be not 0?


    After looking at this again, I also feel that I am missing a step to distinguish for this report, or to create a dashboard with this report...

    Would it be better if in the metric sheet I separated the formular to be 1 on each line for each payment? ie, 1 for deposit, 1 for 50% etc? Then the dashboard will have an easier time identifying which payment was made?

  • Bret Kennedy
    Options

    I went ahead and tried a few things, we have decided that we can work out a way to get the whole thing on 1 sheet, which is change orders and the overall bill. I have also updated the formula to reflect this, but for some reason it isn't adding up the costs, after I have checked the boxes.

    This is my formula, based on the above, and the sheets are attached.


    =SUMIFS({Bakersfield Projects 2023 Range 4}, {Bakersfield Projects 2023 Range 4}, @cell = 1, {Bakersfield Projects 2023 Range 4}, AND(IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!