Is it possible to count the number of entries in a column for the current work week, and quarter?

Adriane Price

Is it possible to count the number of badge IDs in a column for the current work week, and a quarter? I need to track the progress of a weekly target that resets every Friday.

Currently, I can not get the CWs (blue) to align this is my starting point for all of our contract workers.

Here is one of my sheets created that we use for the dashboard (above picture)

Test Dashboard Data - Trending screenshot

Formula used in the above sheet Test Dashboard Data - Trending

Then we have a quarterly which I have created three columns and all three columns do not have the correct count.

CWs Formula

All CWs Formula

Tried pulling a link in from my Test Dashboard Data - Trending

Jeff Reisman

I don't understand why you think this is the wrong result. You're taking your same formula that resulted in 2946, and telling it to add the value in Onboarded@row, which is 5.

2946 + 5 = 2951

Or put another way: 2946 - 0 + 5 = 2951

You do need to add at least a parentheses to the end. I drew in a comma, a space, two quotes, and an end parentheses.

Unless you're trying to get it to add the number of offboarded to the number of onboarded and subtract that from 2946, to give you 2946 - (0 + 5) = 2941 ??? That wouldn't make logical sense though for what you've stated you're trying to achieve.

Jeff Reisman

@Adriane Price OK follow along with me. Let's call this Formula A:

This formula has given me the correct count of 2,946 contracted workers for the workweek (WW) 17.

=IF(TODAY() - [Week Start Date]@row > 0, COUNT({HR Line Manager Tracker - CW Dell Id}) - Offboarded@row, " ")

You say the formula above has given you the correct worker count for week 17, yes? And that number is 2946, correct?

Then you say that you changed it to Formula B:

=IF(TODAY() - [Week Start Date]@row > 0, COUNT({HR Line Manager Tracker - CW Dell Id}) - Offboarded@row + Onboarded@row

and it gave you 2951. If Formula A included the number of onboarded already, then all you did was change the formula to ADD the Onboarded value to your total AGAIN. Notice that the italicized portions of the two formula above are identical.

So, if COUNT({HR Line Manager Tracker - CW Dell Id}) includes the number of Onboarded workers and equals 2946, then Formula A should be:

=IF(TODAY() - [Week Start Date]@row > 0, COUNT({HR Line Manager Tracker - CW Dell Id}), "")

which would equal 2946. To subtract the number of Offboarded workers, Formula B should be:

=IF(TODAY() - [Week Start Date]@row > 0, (COUNT({HR Line Manager Tracker - CW Dell Id}) - Offboarded@row), "")

which would also equal 2946, because the number of Offboarded@row (in your screenshot) is 0.

Jeff Reisman

@Adriane Price

First - can you define the ranges you are using? {HR Line Manager CW Tracker Range 6} and {Test Dashboard Data - Trending Range 15} don't mean much to someone trying to troubleshoot. Maybe give us your formulas with the values you're expecting vs what you're getting? (As a best practice, I try to replace "Range #" with the column name or an abbreviation of the column name. It makes troubleshooting easier, especially months or years later when trying to figure out how you made something work!)

Based on what you have above, shouldn't this formula end with + [Total Onboarded]@row instead of minus? That could be throwing all your numbers off.

Adriane Price

@Jeff Reisman - Thank you, sorry it has taken me so long to reply. My end goal is to get this equation: [Head Count of the prior week] - [offboarded this week] + [onboarded this week] = [Head Count of this week]

Since I did not originally create these sheets I needed to go back in and rename a lot of them to make heads or tails of what belonged to what. Plus I have found that my fiscal workweeks are not accurate.

This formula has given me the correct count of 2,946 contracted workers for the workweek (WW) 17.

=IF(TODAY() - [Week Start Date]@row > 0, COUNT({HR Line Manager Tracker - CW Dell Id}) - Offboarded@row, " ")

Sheet created to calculate per week global count.

The data source sheet.

When I change it to

=IF(TODAY() - [Week Start Date]@row > 0, COUNT({HR Line Manager Tracker - CW Dell Id}) - Offboarded@row + Onboarded@row

This formula has given me an incorrect count of 2,951 contracted workers for the workweek (WW) 17.

Even though the first count is correct, the formula that I created does not provide the weekly total count as the executives are expecting. They are looking at a net count per end of the week and per end of the quarter. Yet it is supposed to all match up to the 2,946 that is currently showing as of today 5/25 or depending on the next upload of the new contracted worker count.

The below example is what the executives are thinking with fictitious numbers inserted to show an example. I am pretty sure I have overthought this entire thing by now. In the beginning, we did not have any dates or numbers to establish a true base count.

The executives are looking for a trending and yet want counts to be a part of some of this dashboard that I create from these sheets.

Jeff Reisman

I don't understand why you think this is the wrong result. You're taking your same formula that resulted in 2946, and telling it to add the value in Onboarded@row, which is 5.

2946 + 5 = 2951

Or put another way: 2946 - 0 + 5 = 2951

You do need to add at least a parentheses to the end. I drew in a comma, a space, two quotes, and an end parentheses.

Unless you're trying to get it to add the number of offboarded to the number of onboarded and subtract that from 2946, to give you 2946 - (0 + 5) = 2941 ??? That wouldn't make logical sense though for what you've stated you're trying to achieve.

Adriane Price

@Jeff Reisman - It is incorrect because there are a total of 2,946 contracted workers and that number already included the onboarded contracted workers.

Jeff Reisman

@Adriane Price OK follow along with me. Let's call this Formula A:

This formula has given me the correct count of 2,946 contracted workers for the workweek (WW) 17.

=IF(TODAY() - [Week Start Date]@row > 0, COUNT({HR Line Manager Tracker - CW Dell Id}) - Offboarded@row, " ")

You say the formula above has given you the correct worker count for week 17, yes? And that number is 2946, correct?

Then you say that you changed it to Formula B:

=IF(TODAY() - [Week Start Date]@row > 0, COUNT({HR Line Manager Tracker - CW Dell Id}) - Offboarded@row + Onboarded@row

and it gave you 2951. If Formula A included the number of onboarded already, then all you did was change the formula to ADD the Onboarded value to your total AGAIN. Notice that the italicized portions of the two formula above are identical.

So, if COUNT({HR Line Manager Tracker - CW Dell Id}) includes the number of Onboarded workers and equals 2946, then Formula A should be:

=IF(TODAY() - [Week Start Date]@row > 0, COUNT({HR Line Manager Tracker - CW Dell Id}), "")

which would equal 2946. To subtract the number of Offboarded workers, Formula B should be:

=IF(TODAY() - [Week Start Date]@row > 0, (COUNT({HR Line Manager Tracker - CW Dell Id}) - Offboarded@row), "")

which would also equal 2946, because the number of Offboarded@row (in your screenshot) is 0.

Adriane Price

@Jeff Reisman - oh my I definitely have been looking at this one too long. Got it, thank you for pointing that out.