Countifs formula help needed

Lee Ratzlaff

Hello!

I need a way to count the total number of records associated with a specific person, in a grid separate from where the data is stored and can't quite figure it out.

Each record has an Assigned Staff column, it is a contact list. Each individual submits data for each client once a month, each record has a reporting period date column. They can have as few as one client or as many as ten clients. I need to count how many records each staff submitted for the previous month. I have made a grid that includes a cell link to populate the email addresses of all employed staff.

What syntax would I need for it to create a metric that displays the total caseload for each staff on a monthly basis?

Here's an example of a different formula that counts all records from last month who were actively enrolled and involved with PS =COUNTIFS({ND CYFD}, "Protective Services", {ND Enrollment Status}, "Active", {ND Reporting Period}, MONTH(@cell) = (MONTH(TODAY()) - 1))

I just need to figure out how to write the syntax so it references the cell with the staff email address instead of being involved with PS.

Thanks!

Paul Newcome

Sorry about that. I missed another piece. it should be [Primary Column]@row not @cell.

Paul Newcome

You would change that first range to a new range that covers the staff email address and then specify the email address as the criteria.

Lee Ratzlaff

This is what I tried, but it's just calculating a zero for each, which is not accurate.

=COUNTIFS({All Faith BC Assigned WF}, "[Primary Column]@cell", {All Faith BC Enrollment Status}, "Active", {All Faith BC Reporting Period}, MONTH(@cell) = (MONTH(TODAY()) - 1))

The Assigned WF is a contact field in the main data set, but the other grid I am referencing has two fields: the WF Full Name (text field) and the primary email address (contact list).

Paul Newcome

Try removing the quotes from around [Primary Column]. Using quotes like that means it is looking for that exact text string but removing the quotes makes it a cell reference so that it will look for the data in that cell.

Lee Ratzlaff

=COUNTIFS({All Faith BC Assigned WF}, [Primary Email]@cell, {All Faith BC Enrollment Status}, "Active", {All Faith BC Reporting Period}, MONTH(@cell) = (MONTH(TODAY()) - 1))

this throws "unparseable"

The [Primary Email] field is a linked cell that pulls from a contact list grid - the sheet reference for Assigned WF is from the main dataset

Paul Newcome

Sorry about that. I missed another piece. it should be [Primary Column]@row not @cell.

Lee Ratzlaff

The change to @row fixed it! Thank you so much, Paul!

Paul Newcome

Happy to help. 👍️