Is it possible to count the number of entries in a column for the current work week, and quarter?
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
Adriane
Best Answers
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@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.
Adriane
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman - It is incorrect because there are a total of 2,946 contracted workers and that number already included the onboarded contracted workers.
Adriane
-
@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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman - oh my I definitely have been looking at this one too long. Got it, thank you for pointing that out.
Adriane
Help Article Resources
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
Check out the Formula Handbook template!