Count days of the week

Loucurr

New Member
I have a sheet with a column containing the date format of "m/d/yy dddd". I want to be able to count the number of occurances of each day of the week.

I tried to format the next column as =text([cell], dddd). It works but the problem with this is that when it is copied into a row that does not currently have a corresponding date the entered the reference reads [saturday] and this will throw off my stats.

Any ideas???

Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

barry houdini

MrExcel MVP
If you have dates in A1:A100 this will count the number of Saturdays and ignore blanks

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

For other days use 1 to 6 (Sunday to Friday)

Loucurr

New Member
This formula returns a #VALUE! error

barry houdini

MrExcel MVP
If all cells in the range contain dates or blanks then that formula should work.....but if you have text in that range you might get a #VALUE! error. Can you post the exact formula you tried?

This variation would ignore text (unless that text is a day of the week!)

=SUMPRODUCT((TEXT(A1:A100,"ddd")="sat")*(A1:A100<>""))

Or......you could try your original approach but filter out blanks, e.g. use a helper column with a formula like

=IF(A1="","",TEXT(A1,"ddd"))

then use COUNTIF on the helper column.......

Loucurr

New Member
This is the formula starting with E2 because E1 is the column heading.

=SUMPRODUCT((WEEKDAY(E2:E100)=7)*(E2:E100<>""))

barry houdini

MrExcel MVP
OK, formula looks great. If it gives a #VALUE! error I believe that can only happen if you have either #VALUE! errors in E2:E100 or, as previously advised, some of E2:E100 are not dates or blanks (in which case WEEKDAY gives an error).

I suggest you check that all entries are dates. COUNT(E2:E100) should correspond to the number of dates in the range. Otherwise try one of the other options I suggested.....

Loucurr

New Member
Ok, I tried this and it works, blank cells are blank when there is no date.

=IF(A1="","",TEXT(A1,"ddd"))

then use COUNTIF on the helper column.......

Now, the COUNTIF function is not counting the occurances.

Some times youy just want to beat your computer!

Thanks for the help thus far.

barry houdini

MrExcel MVP
Assuming your helper column is column B you'd have to use a formula like

=COUNTIF(B:B,"Sat")

Loucurr

New Member
Barry,

Thanks alot! sometimes it is the small things you overlook. I forgot to reference the worksheet when I set up the formula.

Works great!

Yogi Anand

MrExcel MVP
Hi LouCurr:

Following is a solution using EXCEL's one-variable DataTable feature ...

Excel Workbook
ABCDEFG
1Date1*****datBase
212/01/08 Saturday*FALSE***criteria
35/02/08 Tuesday*****DataTable columnInputCell
429/02/08 Friday*DataTable0**DataTable Formula
524/03/08 Monday*Sunday0***
617/04/08 Thursday*Monday2***
7**Tuesday3***
84/06/08 Wednesday*Wednesday1***
928/06/05 Tuesday*Thursday1***
1022/07/08 Tuesday*Friday2***
1115/08/08 Friday*Saturday1***
128/09/08 Monday******
13*******
Sheet3

</body></html>

Replies
4
Views
460
Replies
0
Views
475
Replies
3
Views
370
Replies
15
Views
322
Replies
1
Views
136

1,191,191
Messages
5,985,208
Members
439,947
Latest member
fabiannic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back