Counting Unique Number of Days

[Smokeyham]

I have a table with a large number of readings. Multiple readings are taken per day. I am trying to count the number of each week day on which we have taken readings, for instance "How many Saturdays have we been taking readings on."

I have the dates entered into Column A.

I tried the array formula =SUM(--(WEEKDAY(A1:A200)=7)) Where "7" represents Saturdays, but this gives me very high results. I suspect it is just counting all rows where the day is a Saturday.

Any suggestions?

Many thanks.

Gordon

 

[Momentman]

Something like this maybe

Excel 2010
	A	B	C	D	E
1	item	Date	Sales
2	Item D	19-Nov-17	15		7
3	Item B	12-Nov-17	24
4	Item A	13-Oct-17	18
5	Item D	06-Oct-17	20
6	Item D	11-Nov-17	17
7	Item C	19-Nov-17	19
8	Item D	26-Sep-17	11
9	Item D	27-Nov-17	21
10	Item C	03-Dec-17	24
11	Item B	30-Nov-17	24
12	Item A	29-Oct-17	20
13	Item C	12-Nov-17	15
14	Item A	06-Dec-17	16
15	Item C	15-Oct-17	24
16	Item B	02-Nov-17	20
17	Item C	23-Sep-17	17
18	Item B	23-Sep-17	24
19	Item C	27-Oct-17	18
20	Item A	01-Nov-17	12
21	Item D	01-Nov-17	20
22	Item A	24-Oct-17	24
23	Item D	02-Dec-17	14
24	Item B	05-Dec-17	19
25	Item A	17-Nov-17	23
26	Item B	17-Nov-17	23
27	Item B	10-Nov-17	11
28	Item B	05-Oct-17	19
29	Item B	10-Dec-17	13
30	Item A	18-Oct-17	21
31	Item C	18-Oct-17	14
32	Item B	24-Nov-17	21
33	Item C	01-Dec-17	23
34	Item B	01-Dec-17	15
35	Item A	08-Oct-17	22
36	Item A	18-Nov-17	11
Sheet4

Cell Formulas
Range	Formula
E2	=SUMPRODUCT((1/COUNTIF($B$2:$B$36,$B$2:$B$36))*(WEEKDAY($B$2:$B$36,2)=7))

 

[Aladin Akyurek]

Would you post a small sample along with the desired output?

 

[Smokeyham]

Something like this maybe

</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">1/COUNTIF(<font color="Green">$B$2:$B$36,$B$2:$B$36</font>)</font>)*(<font color="Red">WEEKDAY(<font color="Green">$B$2:$B$36,2</font>)=7</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Thank you! That worked!

One interesting thing is that when I substitute the specified range (=SUMPRODUCT((1/COUNTIF($A$2:$A$349,$A$2:$A$349))*(WEEKDAY($A$2:$A$349,2)=T3))

with a named range (=SUMPRODUCT((1/COUNTIF(ObsCount[[#All],[Date]],ObsCount[[#All],[Date]]))*(WEEKDAY(ObsCount[[#All],[Date]],2)=T2))

I get a syntax error. The reference at the end of the equation (T2 or T3) is a reference to a cell with the day number.

Book1
	T	U	V
1	Day#	Day	Number of Days
2	2	Monday	#VALUE!
3	3	Tuesday	7
4	4	Wednesday	8
5	5	Thursday	6
6	6	Friday	7
7	7	Saturday	4
8	1	Sunday	7
OccupancyRecords

Cell Formulas
Range	Formula
V2	=SUMPRODUCT((1/COUNTIF(ObsCount[[#All],[Date]],ObsCount[[#All],[Date]]))*(WEEKDAY(ObsCount[[#All],[Date]],2)=T2))
V3	=SUMPRODUCT((1/COUNTIF($A$2:$A$349,$A$2:$A$349))*(WEEKDAY($A$2:$A$349,2)=T3))
V4	=SUMPRODUCT((1/COUNTIF($A$2:$A$349,$A$2:$A$349))*(WEEKDAY($A$2:$A$349,2)=T4))
V5	=SUMPRODUCT((1/COUNTIF($A$2:$A$349,$A$2:$A$349))*(WEEKDAY($A$2:$A$349,2)=T5))
V6	=SUMPRODUCT((1/COUNTIF($A$2:$A$349,$A$2:$A$349))*(WEEKDAY($A$2:$A$349,2)=T6))
V7	=SUMPRODUCT((1/COUNTIF($A$2:$A$349,$A$2:$A$349))*(WEEKDAY($A$2:$A$349,2)=T7))
V8	=SUMPRODUCT((1/COUNTIF($A$2:$A$349,$A$2:$A$349))*(WEEKDAY($A$2:$A$349,2)=T8))

 

[Smokeyham]

Thank you! That worked!

One interesting thing is that when I substitute the specified range (=SUMPRODUCT((1/COUNTIF($A$2:$A$349,$A$2:$A$349))*(WEEKDAY($A$2:$A$349,2)=T3))

with a named range (=SUMPRODUCT((1/COUNTIF(ObsCount[[#All],[Date]],ObsCount[[#All],[Date]]))*(WEEKDAY(ObsCount[[#All],[Date]],2)=T2))

I get a syntax error. The reference at the end of the equation (T2 or T3) is a reference to a cell with the day number.

Book1
	T	U	V
1	Day#	Day	Number of Days
2	2	Monday	#VALUE!
3	3	Tuesday	7
4	4	Wednesday	8
5	5	Thursday	6
6	6	Friday	7
7	7	Saturday	4
8	1	Sunday	7
OccupancyRecords

Cell Formulas
Range	Formula
V2	=SUMPRODUCT((1/COUNTIF(ObsCount[[#All],[Date]],ObsCount[[#All],[Date]]))*(WEEKDAY(ObsCount[[#All],[Date]],2)=T2))
V3	=SUMPRODUCT((1/COUNTIF($A$2:$A$349,$A$2:$A$349))*(WEEKDAY($A$2:$A$349,2)=T3))
V4	=SUMPRODUCT((1/COUNTIF($A$2:$A$349,$A$2:$A$349))*(WEEKDAY($A$2:$A$349,2)=T4))
V5	=SUMPRODUCT((1/COUNTIF($A$2:$A$349,$A$2:$A$349))*(WEEKDAY($A$2:$A$349,2)=T5))
V6	=SUMPRODUCT((1/COUNTIF($A$2:$A$349,$A$2:$A$349))*(WEEKDAY($A$2:$A$349,2)=T6))
V7	=SUMPRODUCT((1/COUNTIF($A$2:$A$349,$A$2:$A$349))*(WEEKDAY($A$2:$A$349,2)=T7))
V8	=SUMPRODUCT((1/COUNTIF($A$2:$A$349,$A$2:$A$349))*(WEEKDAY($A$2:$A$349,2)=T8))

Found the solution to the syntax error. If you run across this, just be sure to only choose data within your named column (not including the header row). Here is the updated version of the formula, which works.

=SUMPRODUCT((1/COUNTIF(ObsCount[Date],ObsCount[Date]))*(WEEKDAY(ObsCount[Date],2)=E17)). Notice the absence of "[#ALL]" in the formula.