How to count specific days

Hello!

I have a log-in sheet for people who come into my office. I need to find a way to count how many people came in on each day of the week, M-F, so that I can find out the most popular day of the week for our service is. Please help!

Thanks!

Thank you for the suggestion! I just uploaded an example of the spreadsheet.

Originally Posted by bwhi

I just uploaded an example of the spreadsheet.

Where? I don't see it.

To upload an Excel workbook, click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I apologize, this is my first time on this forum so I'm trying to figure it out. I uploaded my .xls file, however when I try to attach it to my reply, it didn't seem to work. It should be there now!

Umm, I can't see relationship between your data and result.

What are you counting? Just how many times each weekday occurs in column A without any other condition?

Yes that's correct. I have a lot of other things going on with my original spreadsheet, such as how many times people come in from each specific town each month, how many people had which item, etc.

But what I'm stuck on is how many people came in on each weekday. So in the end I will have a graph for all of 2017 that shows what days of the week are the most popular for our service.

You can probably use below. However, depending on your data size, it's going to be resource intensive calculation.

If that becomes an issue... I'd recommend adding helper column in source data (=Text(A2,"dddd")) and using pivot table to summarize.

In R3:
=SUM(IF(TEXT($A$2:$A$50,"dddd")=Q3,1,0))
Confirmed as Array (CTRL + SHIFT + ENTER) copy down.


Or non array version using sumproduct.
=SUMPRODUCT((TEXT($A$2:$A$50,"dddd")=Q3)*1)

With your sample returns. 0, 16, 16, 12, 5 for Mon to Fri respectively.

Thank you, the second formula you gave me worked perfectly for what I needed

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.