Sheet Summary NETDAYS referencing another sheet summary formula

Mick Tim

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!

Genevieve P.

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

Mick Tim

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.

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