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.