Count Weekdays

**AlinaH** · 08-01-2012, 05:34 PM

I want to count the number of times a particular weekday appears in my column. I have generated the day of the week by changing the cell formatting to dddd. Now that I can see the days, I would like to perform a countif function. I am unable to use the DOW as the criteria since excel is reading the cell as a numerical date. What formula can I use? I know Sunday=1, Monday=2 etc... I'm stuck...

Thanks!

**BenMiller** · 08-01-2012, 05:38 PM

You can format the cells however you want ... with your dates in column A, try

=SUMPRODUCT(--(WEEKDAY(A1:A100)=3))

**daddylonglegs** · 08-01-2012, 05:40 PM

You can keep the date formatting if you want (formatting doesn't make any difference) and then count using WEEKDAY function, e.g. for dates in A2:A100 this will count Sundays

=SUMPRODUCT((WEEKDAY(A2:A100)=1)+0)

Change the 1 to 2, 3 etc. for other days

Any blank cells are normally identified as Saturdays (!) so for Saturday count you might want to exclude blanks

=SUMPRODUCT((WEEKDAY(A2:A100)=7)*(A2:A100<>""))

**AlinaH** · 08-02-2012, 02:35 PM

The equations work if the data is generating from the same sheet. I am using a column of dates that are linked to another worksheet from the same workbook using an if statement (=IF(ISNUMBER(N101),'ED&IMM Worksheet'!J102,"-")). I think that the equation is reading the equation in the cell instead of the values (day of the week) because when I use the exact equations that work to sum the linked values I receive a Value! error. I don't know what else to do....

Thanks again!

**BenMiller** · 08-02-2012, 02:45 PM

If that's your equation, that means you have some cells with "-" in them. That'll give you a #VALUE error. As daddylonglegs pointed out, you need to have either numbers or blanks in your cells. Can you instead use

=IF(ISNUMBER(N101),'ED&IMM Worksheet'!J102,"-")

and format the result as accounting, to get the little dash you now have ...?

You'll now have to use

=SUMPRODUCT((WEEKDAY(A2:A100)=7)*(A2:A100<>"")*(A2:A100<>0))

for Saturdays.

**daddylonglegs** · 08-02-2012, 03:15 PM

Originally Posted by BenMiller

=IF(ISNUMBER(N101),'ED&IMM Worksheet'!J102,"-")

I assume you meant to have a zero at the end there, Ben, i.e.

=IF(ISNUMBER(N101),'ED&IMM Worksheet'!J102,0)

Another way is to leave the column H formulas as they are and count using TEXT function, i.e.

=SUMPRODUCT((TEXT(A2:A100,"ddd;;")="Sat")+0)

That won't get tripped up by dashes (or blanks)

**BenMiller** · 08-02-2012, 03:19 PM

Yes, my mistake ... copied/pasted original formula but forgot to change it.

And I like the TEXT solution ... that's great!

**AlinaH** · 08-02-2012, 03:32 PM

The last formula worked. However, do I need to add +1 for Sat? When I tested the formula on different columns everything added up except that Sat was short by 1 each time.

Thanks so much everyone!!!!!!!

**daddylonglegs** · 08-02-2012, 04:03 PM

I think it should count all Saturdays, are you sure the range you are using covers all the dates?

**AlinaH** · 08-02-2012, 04:16 PM

Yes, all cells are included in the range. Also, the number of Saturdays is consistently off by 1 value as I copy the equation for each column.

thank you again