![]() |
|
#1
|
|||
|
|||
![]()
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. |
#2
|
|||
|
|||
![]()
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. |
#3
|
||||
|
||||
![]()
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 |
#4
|
|||
|
|||
![]()
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 |
#5
|
|||
|
|||
![]()
Ray,
Thanks for feedback and good luck with the project. |
![]() |
Tags |
average and sum, count, weekday |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
tinfanide | Word VBA | 4 | 01-19-2013 11:31 PM |
'Linking' entered information to other "cells" from an original "cell" in MS Word | Wade | Word | 6 | 09-03-2012 05:22 PM |
![]() |
Jamal NUMAN | Word | 2 | 07-03-2011 03:11 AM |
![]() |
Learner7 | Excel | 1 | 04-25-2011 04:39 AM |
Excel error: "Too many different cell formats" | enviroko | Excel | 0 | 01-09-2008 07:27 AM |