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

AmeliaP

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

Julia Bennfors

@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

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.

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