Count Query
Hi,
I have a sheet that tracks Daily attendance. I have a 'Name' field and 'Date' field. How would I gather a daily and weekly count of unique names?
Furthermore I have a 'manager' field - Is it possible to provide a daily total for each time the managers name appears in this column.
Thanks
Answers
-
Good Morning Karl Twist
I have a sheet that does just what you are requesting.
On your daily attendance sheet add a column and put this formula in it. ="your Date Column"+"your Name Column" copy formula down.
You have to create a new sheet and create the column's above. In the primary Column you have to list all the names that you want to track. Next in the "Week Of" Column (Make sure it is a Date Column) you have to enter any monday's Date. In "Tuesdays column" write this formula =[Week of]@row + 1 then in "Wednesday column" =[Tuesday1]@row + 1 "Thursday's Column" =[Wednesday1]@row + 1. By now you see the pattern continue this pattern till you get to sunday. Next in Column16 write this formula =[Week of]$2 + [Primary Column]2 then in Column17 =[Tuesday1]$2 + [Primary Column]2 again by now you see the pattern continue the pattern till you get to column22. Note: make sure to put the $ to make it absolute in the formula so when you copy down it will work correctly. now copy the formula down to the last name on your sheet at this point you should see something that looks like this 03/01/21John Doe.
Now in Day1 write this formula =COUNTIF("Reference The column that you create on your attendance sheet", [Column16]@row) Day2 =COUNTIF("Reference The column that you create on your attendance sheet", [Column17]@row) again you see the pattern continue till you get to day7. Copy The formula down and you are done. You can hide column16-22 and Tuesday -Sunday. Do not hide Week of. this is where you will change the date for new week.
Hope this helps
-
Thanks for the reply. I get an error with the '=[Week of]@row + 1' formula as it returns a #invalid column value error. Any ideas? Thanks
-
Make sure week of, tuesday, wednesday, thursday, friday, saturday, sunday are all Date Columns
-
Thank You,
Can you please help with the reference to the original sheet. The sheet i'm referencing is named 'Desk Booking Form' and the New Column is 'Date Calc'.
Appreciate your help so far.
-
Do you have teams so I can walk you thru it.
-
I do but unavailable for the next 3hrs as in a meeting. If you can't advise via here then I would appreciate catching up on this topic via MS Teams tomorrow. Thank You again!
-
Click on Reference another sheet. select the sheet name. This will pull up your sheet click on the column that you created and click insert reference.
It should look like this =COUNTIF({Desk Booking Form Range 1}
after the close bracket type a "," then click on the first cell in Day1 column and hit enter
the end result is =COUNTIF({Desk Booking Form Range 1},[Column16]@row)
-
Repeat for Day2-Day7 but for
Day2 =COUNTIF({Desk Booking Form Range 1},[Column17]@row)
Day3 =COUNTIF({Desk Booking Form Range 1},[Column18]@row)
Day4 =COUNTIF({Desk Booking Form Range 1},[Column19]@row)
all the way to Day7
then you select all the columns Day1-Day7 and copy formula down to last row.
-
Thank You!
The formula now populates but all results are just returning 0...
-
Make sure the "Week of" date is in the past and starts on a monday
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!