Countifs formula help needed
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!
Best Answer
-
Sorry about that. I missed another piece. it should be [Primary Column]@row not @cell.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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).
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
=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
-
Sorry about that. I missed another piece. it should be [Primary Column]@row not @cell.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
The change to @row fixed it! Thank you so much, Paul!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives