Sheet Summary NETDAYS referencing another sheet summary formula

Mick Tim
Mick Tim ✭✭
edited 09/22/22 in Formulas and Functions

Hi,

I cannot quite find a solution I require for this question in the community. Hopefully someone with more knowledge can help.

I have H&S sheet logging any incidents, accidents, near misses etc. I am trying to build good sheet summary data to pull metrics through to a dashboard.

I have already completed Date of Last "X" and Days Since Last "X" based on the previous summary using MAX(COLLECT for date and "accident" I have then referenced that summary formula in a NETDAYS formula to count days since last "accident" (see image).

However, we have multiple sites and projects and I need a way to pull these metrics for individual sites in a single sheet. For example, in the summary, I would need metrics for "Factory 1", "Factory 2" etc.

I cannot make a multiple criteria MAX(COLLECT work to based it on last date, site and type of incident. I have been struggling to figure it out for the last couple of days.

I have:

=MAX(COLLECT([Date Raised]:[Date Raised], [Accident, Incident or Near Miss]:[Accident, Incident or Near Miss], CONTAINS("Accident", @cell)))

Followed by:

=NETDAYS([Date of Last Accident All Sites]#, TODAY())

Can someone please help me find a solution?

Thanks!


Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Mick Tim

    The COLLECT function should allow you to enter a new range (column) and criteria to make it have multiple criteria.

    Structure example:

    =MAX(COLLECT([Column to MAX]:[Column to MAX], [First Column]:[First Column], "First Criteria", [Second Column]:[Second Column], "Second Criteria"))


    This means you should be able to add on to your working formula by listing the Site column and what Factory you're looking for, like so:

    =MAX(COLLECT([Date Raised]:[Date Raised], [Accident, Incident or Near Miss]:[Accident, Incident or Near Miss], CONTAINS("Accident", @cell), Site:Site, "Factory 1"))


    If this doesn't work, can you post the formula you're currently trying and the error message you're receiving?

    Thanks!

    Genevieve

Answers

  • I have managed to create a formula to pull the date of the last Accident, Incident or Near Miss for Factory 1 using this:

    =MAX(COLLECT(Date:Date, Site:Site, Site1))

    However, I need to be able to drill down the criteria for each type. I have attempted pre-fixing this with an IF statement for "Accident" and it comes back with UNPARSEABLE or INCORRECT ARGUMENT SET.

    I have also tried =MAX(COLLECT(AND to combine the date, site and type but no variation has worked for me so far.

    Thanks,

    M

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Mick Tim

    The COLLECT function should allow you to enter a new range (column) and criteria to make it have multiple criteria.

    Structure example:

    =MAX(COLLECT([Column to MAX]:[Column to MAX], [First Column]:[First Column], "First Criteria", [Second Column]:[Second Column], "Second Criteria"))


    This means you should be able to add on to your working formula by listing the Site column and what Factory you're looking for, like so:

    =MAX(COLLECT([Date Raised]:[Date Raised], [Accident, Incident or Near Miss]:[Accident, Incident or Near Miss], CONTAINS("Accident", @cell), Site:Site, "Factory 1"))


    If this doesn't work, can you post the formula you're currently trying and the error message you're receiving?

    Thanks!

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!