# Countifs formula help needed

Options
edited 11/07/22

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!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• Options

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).

• ✭✭✭✭✭✭
Options

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.

• Options

=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

• ✭✭✭✭✭✭