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

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

Answers

  • @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 ✭✭

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

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!