I know this must be easy, but I am missing something as I am trying to work through this.



My staff are tracking how many 'same day appointments' we get on a specific day. They put together this spreadsheet (which was easy for them) and the "X" represent an appointment made on the same day.
<sameday_apt.png
What I want to be able to do is to 'count' the number of "X" in a cell, and then categorize by day of the week, so how many same day appointments are on Monday AM, and Monday pm in April, or May, or for a quarter?, and how many on average for a quarter.

I believe I can do the last part - summing and averaging. But getting the total in a day of week format....

I put together this chart with the 'weekday' function that tells me which day of the week the days are,



I just need some help putting it all together.

I am thinking something like "=COUNTIF(J6,"X")" (without quotes) but it does not count the "X" in the cells.
Then I still have to figure out the way to separate 'weekday2, weekday 3, etc, but i think I can figure that out if I can count the number of 'X' in the cell

Thanks in advance for any help.

rayramirezdvm,

Firstly, not 100% idiot proof but you could count the X's by using e.g. for your C11 =LEN(C11)
It will actually count any characters but you will have to rely on user entry discipline to avoid spaces or maybe use..... =LEN(SUBSTITUTE(C11," ",""))

You could use SUMPRODUCT to compute some of the stats you want but given the variety of stats you mention above I think it would be worth restyling your raw data and then exploring the use of a pivot table.

Below is a link to one of a series of video tutorials (You Tube abounds with such things) that might be worth you watching.

https://www.youtube.com/watch?v=8ffdXfriLPQ
Hope that helps.

Also get rid of the merged cells for the months ( well, I suppose they are merged cells). There is little one can do with pictures except admire the colours.

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thank you both!
I did rearrange the data, although not what the staff enter. They are _not_ tech savvy, so since they do this correctly, I wanted to keep it familiar.

I love that Excel can let me put the same data in a different cell, and that is what I did.

And the pivot table tutorials are amazing! I had a very crude knowledge of how they worked, but this has opened up several things that I want to do with other data that I have!

I have not completely got it figured out, but I am very close to getting the data in a format that will actually be helpful!
Thank you all so much!
Ray

Ray,
Thanks for feedback and good luck with the project.