count/sum formula by week/area

Francis Chinchar

I am trying to create a data sheet so that i can display on a dashboard the summary results of an audit, performed by the week.

The original data is captured by a form into a sheet name MIF-Safety Observation in this I have the following fields:

Week # =WEEKNUMBER(Created@row)

Area # =LEFT([OIT Boards]@row, 7)

Audit Type (only two options) Planned or Un-planned

Shift (only four options) 1st or 2nd or 3rd or Weekend

Fail =COUNTIF([PPE-Eyes]@row:Chains@row, "Fail")

Pass =COUNTIF([PPE-Eyes]@row:Chains@row, "Pass")

Audits =Fail@row + Pass@row

=SUM(Pass@row) / (Fail@row + Pass@row)

What i am trying to accomplish is to have the data presented in a fashion that it provides a weekly summary for each area.

Area Week # Pass Fail Score

Area 01 01 5 0 100%

02 3 1 66%

03 4 3 25%

In my GRID extract sheet I can get this formula to work

=COUNTIF({MIF-Safety Observation Range 1}, "Planned") + " Planned" + " / " + COUNTIF({MIF-Safety Observation Range 1}, "Un-planned") + " Un-planned"

But am failing on all different scenarios when i try using Area

=COUNTIFS({Area}, IFERROR(WEEKNUMBER(@cell), 0) = [Week#]@row)

Paul Newcome

Your Area column doesn't contain a date, so the WEEKNUMBER portion is failing. You would need to either adjust the range to cover a date type column or change your criteria to not be evaluating a number.