# Count Query

Options

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

• ✭✭✭✭✭✭
Options

Good Morning Karl Twist

I have a sheet that does just what you are requesting.

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

• Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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'.

• ✭✭✭✭✭✭
Options

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

• Options

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!

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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.

• Options

Thank You!

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

• ✭✭✭✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!