How can I reference a date from another sheet when writing a SUMIFS formula?

Options

I am trying to reference how many points are ticked as 'complete' per day and pull this in to a new Master.

Answers

  • Julia Bennfors
    Options

    @AmeliaP

    Hey!

    In your Existing Ticket Tracker Sheet, my suggestion is to set it up with a buddy column called "Ticket Closed", if possible, because it will make writing the formula on the new master a little easier/future proofs it a bit, if you ever want to count other durations - ie quarters, months, etc. If you cannot, scroll below.

    The Ticket Closed Column is a checkbox. It will be a Column Formula with this formula (see below).

    =IF([Date Closed]@row <> "", true)

    In your new Master Sheet, assuming you added the Ticket Closed Column. You can write your formula to say "Count if the "ticket closed column" is checked and it happened TODAY"

    =COUNTIFS({Existing Ticket Tracker Sheet: Ticket Closed Colum}, true, {Existing Ticket Tracker Sheet: Date Closed}, TODAY())

    If you want it between certain dates you and duration and just adjust the back end of the formula instead of saying "today"

    Alternative Option: If you could not add the extra column, in the new sheet - use this. It will count how many tickets were closed using the date column.

    =COUNTIF({Existing Ticket Tracker Sheet: Date Closed}, TODAY())

    Let me know if this is what you were looking for! Thanks!

  • AmeliaP
    AmeliaP ✭✭
    Options

    Hello Julia,

    Sorry I am still not 100% sure on how this links to multiple dates using the TODAY function.

    I am currently trying this formula:

    SUMIFS(No. of unit points, units complete tick box, true, production start date, date column in new sheet)

    I need a sum of points complete or incomplete per day.

    Thank you.


  • Genevieve P.
    Options

    Hey @AmeliaP

    Did your SUMIFS formula work?

    =SUMIFS({No. of unit points}, {units complete tick box}, 1, {production start date}, [Production Start Date]@row)

    If not, is it possible that the "Number of Unit Points" column has data that's seen as text instead of a number? It would be helpful to see a screen capture of the source sheet but please block out anything sensitive.

    Thanks,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!