Formula for counting rows as they flow in and out

Hi Everyone,

We have an intake sheet, fed by a form, that assigns rows to staff members. Leadership wants to be able to balance workload by tracking numbers of rows assigned over a timeframe.

On a metric sheet, we use =COUNTIFS({Column Name}, "Person Name") to see how many each person has assigned to them at any given moment.

Given that, as each person completes their tasks, the row moves to a different sheet, how do we capture how many each person was assigned in a month or year?

That is, each person may get assigned and work on a total of 50 rows in a month but at any particular moment might have less than 10 as finished ones move out before more come in.

We do have a system column for "Created Date" and each new row comes in with a Status if "NEW" so we could count those pretty easily. But I don't know how to set up a running total with a count that doesn't decrement when the status is changed to "In Review" or "Approved," etc..

I very much appreciate your time and help with this,

Carroll

Find more posts tagged with

Formulas

Resource Management

dates

Accepted answers

All comments

KPH

Could you count those for each person with a Created Date between the start and end that you are interested in, in both the current and archive sheet? This would need two COUNTIFS functions (criterion of name equals person you want, created date is on or after start of range, and created date is on or after end of range) with a plus sign between them.

Carroll Wall

Hello, thank you for reply.

If I am understanding what you are suggesting it would look like

=(COUNTIFS (Intake sheet created date is target date through target date, Name) + (COUNTIFS(Repository sheet created date is target date through target date, Name))

So if I want monthly counts I need 12 counts for each name?

Plus a 13th for year total

KPH

I was thinking:

=COUNTIFS({Column in intake with name in}, "Person Name",{Column in intake with date in}, MONTH(@cell ) = MONTH(DATE(2025, 1, 1)))

COUNTIFS({Column in archive with name in}, "Person Name",{Column in archive with date in}, MONTH(@cell ) = MONTH(DATE(2025, 1, 1)))

Where the date is a date in the month that you want to count. This would then count the hours for just that month and just that person, first in the intake sheet, then in the archive sheet.

I would do this in a table so the dates and name are references to another cell not hard coded in the formula, then you can use the same formula each time. Like this:

=COUNTIFS({Column in intake with name in}, Jane$1,{Column in intake with date in},

MONTH(@cell ) = MONTH([Month Start]@row ))

+COUNTIFS({Column in archive with name in}, Jane$1,{Column in archive with date in},

MONTH(@cell ) = MONTH([Month Start]@row ))

In a grid like this:

Carroll Wall

Thanks again, this is very helpful.
Can you help me understand what the $1 after the name does? I tried using it in the formula and got the UNPARSEABLE error. I took it out and it works.

KPH

If you have the term in row 1 of the column called Jane, the cell would be Jane1. If you put that formula in row 2, referring to Jane1 and drag the formula down to row 3, the Jane1 would change to Jane2. Adding the $ fixes the row number, so you can drag the formula without incrementing the row number.

If you have a space in your column name the $ goes after the ]

[Jane Smith]$1

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.