Count Query

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

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

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

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)