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
-
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!
-
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.
-
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!