Analysing Data According to day of the week.

[CaptainMarvel4]

I would like to be able to set up a spreadsheet that automatically calculates averages and percentages according to numbers that are put in cells under each day of the week. I am currently using countif formulae, but have to manually expand the range every time a new week passes. The columns i have set up are (see image below):
Names in Cells A, Then dates and days of the week across the top. The numbers placed in them are 1-6. I want to automatcially calculate how many 1s take place for each person on each day of the week to analyse for patterns.

I hope I have explained this well enough. I appreciate any help that can be offered.

 

[6StringJazzer]

You have not shown column or row headings in your screenshot so I will guess that the "Name" cell is A1, and the name of the sheet is Sheet1.

I don't know what's in row 1 so I am going to have to assume that the days of the week are displayed using the TEXT function based on the dates in row 2.

You don't show any formulas in the second grid so I'll assume you don't have any yet, and you just typed in numbers to illustrate what you want. And again, no headers, so I am going to treat it as though it's on another sheet and the blank upper-left gray cell is A1.

You said "for each person" so I am assuming that means you want a separate grid for totals for each person.

For the Monday/1 cell (which is B2), for the first name:

=COUNTIFS(Sheet1!4:4,B$1,Sheet1!$1:$1,$A2)

Then copy to the rest of the grid. Then make a copy of the grid and change 4:4 to 6:6 for the next name, and so forth.

If this doesn't help then please give a more complete description of what your data is, what your formulas are, and what your sheet layout is.

 

[CaptainMarvel4]

You have not shown column or row headings in your screenshot so I will guess that the "Name" cell is A1, and the name of the sheet is Sheet1.

I don't know what's in row 1 so I am going to have to assume that the days of the week are displayed using the TEXT function based on the dates in row 2.

You don't show any formulas in the second grid so I'll assume you don't have any yet, and you just typed in numbers to illustrate what you want. And again, no headers, so I am going to treat it as though it's on another sheet and the blank upper-left gray cell is A1.

You said "for each person" so I am assuming that means you want a separate grid for totals for each person.

For the Monday/1 cell (which is B2), for the first name:

=COUNTIFS(Sheet1!4:4,B$1,Sheet1!$1:$1,$A2)

Then copy to the rest of the grid. Then make a copy of the grid and change 4:4 to 6:6 for the next name, and so forth.

If this doesn't help then please give a more complete description of what your data is, what your formulas are, and what your sheet layout is.

Thank you for your reply:

All of these cells are simply manual entry (bar the average calculation):

This table is just created within the sheet:

The cell with the value 26 in it is created from the formula

=COUNTIF($E$4:$E$18,"1")+COUNTIF($J$4:$J$18,"1")+COUNTIF($O$4:$O$18,"1")+COUNTIF($T$4:$T$18,"1")+COUNTIF($Y$4:$Y$18, "1")

I have asked it to count all the number ones, for all of the people, in all of the cells that corresponding to Mondays

The Cell undernreath with the value 18 in it, is created from the formula:

=COUNTIF($F$4:$F$18, "1")+COUNTIF($K$4:$K$18, "1")+COUNTIF($P$4:$P$18,"1")+COUNTIF($U$4:$U$18, "1")+COUNTIF($Z$4:$Z$19, "1")

I have asked it to count all the number ones, for all of the people, in all of the cells that corresponding to Tuesdays

When new data is added for each day, I then have to manually change the range with a new '=countif' range and I would like to be able to have that process automated, so that when a new set of 'Monday data' is added, it automatcially calculates the total in the Weekly analysis box (The one with Monday to Friday and with 1 - 6 listed)


I have another separate table in the sheet that counts the numbers for each person:

The value of 15 for Person A has a formula of =COUNTIF($C$4:$AD$4,("1"))

I don't know how to do a table with Person A that can calculte the frequency of 1s, 2s, and 3s on specific days - if that is possible it would save me a huge amount of time.

 

[6StringJazzer]

I don't know how to do a table with Person A that can calculte the frequency of 1s, 2s, and 3s on specific days - if that is possible it would save me a huge amount of time.

That's exactly what the formula does that I provided. You just have to review my assumptions and make any changes for assumptions that are violated.

 

[CaptainMarvel4]

Thank you for your help - I feel completely out of my league in trying to understand it properly. I have tried to use the formula you provided but I can't get it to work. Here is the sheet in full so you can see what I am trying to achieve:

In Cell H22 I am trying to get it to add all the 1s that happen on Mondays in the range c4:ad18 (but this will grow when new dates and adat are added; in cell I22 I am trying to get it to add all the 2s that happen on Mondays etc etc

In Cell C30, I am trying to get it to add all the 1s that happen on Mondays for a person whose name will be in B30 and A4 and linked to the data information in row 4. I am hoping that the formula will automatically calculate the new data as it is added each each.

Thank you for your patience.

 

[Asbestos_Jen]

Here are a couple of options. None of them are super elegant, but they all get the job done. Note that option A and C will need to be modified if new people are added, but won't need to be modified for extra dates. Option B will require the formulas in the helper rows to be filled right whenever dates are added, but the formulas in the option B table will not need changes. I would use option B, personally.

Your original data, with helper rows added, and options A and B:

2024-11-19.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Name	Average	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Thursday	Friday	Saturday	Sunday
2			05-Sep	06-Sep	09-Sep	10-Sep	11-Sep	12-Sep	13-Sep	16-Sep	17-Sep	18-Sep	19-Sep	20-Sep	23-Sep	24-Sep	19-Sep	20-Sep	21-Sep	22-Sep
3
4	Person1	3.64	6	6	1	2	6	1	4	1	6	6	1
5
6	Person2	3.00	1	1	1	1	2	6	6	6
7
8	Person3	2.82	4	2	1	1	3	1	2	5	2	5	5
9
10	Person4	4.30	3	6	6	2	3		6	5	2	5	5
11
12	Person5	1.45	1	1	1	1	1	4	1	1	2	1	2
13
14	Person6	1.45	1	1	1	1	1	1	1	1	6	1	1
15
16	Person7	1.91	1	1	1	3	3	3	3	1	1	3	1
17
18	Person8	1.09	1	1	1	1	1	1	2	1	1	1	1
19
20	Helper rows		Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Thursday	Friday	Saturday	Sunday
21		1	5	5	7	5	3	4	2	5	2	3	4	0	0	0	0	0	0	0
22		2	0	1	0	2	1	0	2	0	3	0	1	0	0	0	0	0	0	0
23		3	1	0	0	1	3	1	1	0	0	1	0	0	0	0	0	0	0	0
24		4	1	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	0
25		5	0	0	0	0	0	0	0	2	0	2	2	0	0	0	0	0	0	0
26		6	1	2	1	0	1	1	2	1	2	1	0	0	0	0	0	0	0	0
27
28						A. Without helper rows								B. With helper rows
29	1		45	54%		All People	1	2	3	4	5	6		All People	Monday	Tuesday	Wednesday	Thursday	Friday
30	2		10	12%		Monday	12	0	0	0	2	2		1	12	7	6	13	7
31	3		8	10%		Tuesday	7	5	1	0	0	2		2	0	5	1	1	3
32	4		3	4%		Wednesday	6	1	4	0	2	2		3	0	1	4	2	1
33	5		6	7%		Thursday	13	1	2	2	2	2		4	0	0	0	2	1
34	6		12	14%		Friday	7	3	1	1	0	4		5	2	0	2	2	0
35			84	100%										6	2	2	2	2	4
36
37			1	2	3	4	5	6
38		Person1	4	1	0	1	0	5
39		Person2	4	1	0	0	0	3
40		Person3	3	3	1	1	3	0
41		Person4	0	2	2	0	3	3
42		Person5	8	2	0	1	0	0
43		Person6	10	0	0	0	0	1
44		Person7	6	0	5	0	0	0
45		Person8	10	1	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
C1:T1	C1	=TEXT(WEEKDAY(C2),"Dddd")
D2:P2	D2	=WORKDAY(C2,1)
B4,B18,B16,B14,B12,B10,B8,B6	B4	=AVERAGE(C4:S4)
C20:T20	C20	=C1
C21:C26	C21	=COUNTIF(C$4:C$18,$B21)
D21:T26	D21	=COUNTIF(D$4:D$18,$A29)
C29:C34	C29	=COUNTIF($C$4:$M$18,A29)
D29:D34	D29	=C29/$C$35
C35	C35	=SUM($C$29:$C$34)
D35	D35	=SUM($D$29:$D$34)
G30:L34	G30	=COUNTIFS($4:$4,G$29,$1:$1,$F30)+COUNTIFS($6:$6,G$29,$1:$1,$F30)+COUNTIFS($8:$8,G$29,$1:$1,$F30)+COUNTIFS($10:$10,G$29,$1:$1,$F30)+COUNTIFS($12:$12,G$29,$1:$1,$F30)+COUNTIFS($14:$14,G$29,$1:$1,$F30)+COUNTIFS($16:$16,G$29,$1:$1,$F30)+COUNTIFS($18:$18,G$29,$1:$1,$F30)
O30:S35	O30	=SUMIFS(21:21,$1:$1,O$29)
C38:H38	C38	=COUNTIF($C4:$O4,C$37)
C39:H39	C39	=COUNTIF($C6:$O6,C$37)
C40:H40	C40	=COUNTIF($C8:$O8,C$37)
C41:H41	C41	=COUNTIF($C10:$O10,C$37)
C42:H42	C42	=COUNTIF($C12:$O12,C$37)
C43:H43	C43	=COUNTIF($C14:$O14,C$37)
C44:H44	C44	=COUNTIF($C16:$O16,C$37)
C45:H45	C45	=COUNTIF($C18:$O18,C$37)

Option C (lots of mini tables):

2024-11-19.xlsx
	U	V	W	X	Y	Z	AA
28	C. Lots of mini tables
29	All Persons	1	2	3	4	5	6
30	Monday	12	0	0	0	1	1
31	Tuesday	12	0	0	0	1	1
32	Wednesday	12	0	0	0	1	1
33	Thursday	12	0	0	0	1	1
34	Friday	12	0	0	0	1	1
35
36
37	Person1	1	2	3	4	5	6
38	Monday	2	0	0	0	0	0
39	Tuesday	0	1	0	0	0	1
40	Wednesday	0	0	0	0	0	2
41	Thursday	2	0	0	0	0	1
42	Friday	0	0	0	1	0	1
43
44	Person2	1	2	3	4	5	6
45	Monday	1	0	0	0	0	1
46	Tuesday	1	0	0	0	0	0
47	Wednesday	0	1	0	0	0	0
48	Thursday	1	0	0	0	0	1
49	Friday	1	0	0	0	0	1
50
51	Person3	1	2	3	4	5	6
52	Monday	1	0	0	0	1	0
53	Tuesday	1	1	0	0	0	0
54	Wednesday	0	0	1	0	1	0
55	Thursday	1	0	0	1	1	0
56	Friday	0	2	0	0	0	0
57
58	Person4	1	2	3	4	5	6
59	Monday	2	0	0	0	0	0
60	Tuesday	1	1	0	0	0	0
61	Wednesday	2	0	0	0	0	0
62	Thursday	1	1	0	1	0	0
63	Friday	2	0	0	0	0	0
64
65	Person5	1	2	3	4	5	6
66	Monday	2	0	0	0	0	0
67	Tuesday	1	0	0	0	0	1
68	Wednesday	2	0	0	0	0	0
69	Thursday	3	0	0	0	0	0
70	Friday	2	0	0	0	0	0
71
72	Person6	1	2	3	4	5	6
73	Monday	2	0	0	0	0	0
74	Tuesday	1	0	1	0	0	0
75	Wednesday	0	0	2	0	0	0
76	Thursday	2	0	1	0	0	0
77	Friday	1	0	1	0	0	0
78
79	Person7	1	2	3	4	5	6
80	Monday	2	0	0	0	0	0
81	Tuesday	2	0	0	0	0	0
82	Wednesday	2	0	0	0	0	0
83	Thursday	3	0	0	0	0	0
84	Friday	1	1	0	0	0	0
85
86	Person8	1	2	3	4	5	6
87	Monday	0	0	0	0	0	0
88	Tuesday	0	0	0	0	0	0
89	Wednesday	0	0	0	0	0	0
90	Thursday	0	0	0	0	0	0
91	Friday	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
V30:AA34	V30	=SUMIFS(V$37:V$91,$U37:$U91,$U30)
V38:AA42	V38	=COUNTIFS($1:$1,$U38,$4:$4,V$37)
V45:AA49	V45	=COUNTIFS($1:$1,$U45,$6:$6,V$37)
V52:AA56	V52	=COUNTIFS($1:$1,$U52,$8:$8,V$37)
V59:AA63	V59	=COUNTIFS($1:$1,$U59,$12:$12,V$37)
V66:AA70	V66	=COUNTIFS($1:$1,$U66,$14:$14,V$37)
V73:AA77	V73	=COUNTIFS($1:$1,$U73,$16:$16,V$37)
V80:AA84	V80	=COUNTIFS($1:$1,$U80,$18:$18,V$37)
V87:AA91	V87	=COUNTIFS($1:$1,$U87,$28:$28,V$37)