Count Query

Karl Twist

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

Antonio Figueroa

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

Karl Twist

https://community.smartsheet.com/discussion/comment/281564#Comment_281564

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

Antonio Figueroa

Make sure week of, tuesday, wednesday, thursday, friday, saturday, sunday are all Date Columns

Karl Twist

https://community.smartsheet.com/discussion/comment/281574#Comment_281574

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.

Antonio Figueroa

Do you have teams so I can walk you thru it.

Karl Twist

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!

Antonio Figueroa

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)

Antonio Figueroa

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.

Karl Twist

https://community.smartsheet.com/discussion/comment/281598#Comment_281598

Thank You!

The formula now populates but all results are just returning 0...

Antonio Figueroa

Make sure the "Week of" date is in the past and starts on a monday