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 [email protected], 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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
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})  [email protected], " ")
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})  [email protected] + [email protected]
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})  [email protected]), "")
which would also equal 2946, because the number of [email protected] (in your screenshot) is 0.
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
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})  [email protected], " ")
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})  [email protected] + [email protected]
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 [email protected], 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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
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})  [email protected], " ")
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})  [email protected] + [email protected]
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})  [email protected]), "")
which would also equal 2946, because the number of [email protected] (in your screenshot) is 0.
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
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
Check out the Formula Handbook template!