I need help rolling up multiple weeks of data into one response based on specified criteria

Kishan Naik RA

I currently have a sheet that collects weekly attendance info from multiple stakeholders (for the purpose of this question we can just look at the Coach Info columns). There are 3 responses that can be put into this column: 'Yes', 'Some, and 'None'.

My second sheet is a monthly tracker in which I am trying to read four weeks of data from the weekly Coach Info column and spit out the highest level response with the specific criteria being the student name (if there is a 'Yes' in any of the four cells, take 'Yes', if no 'Yes' but there is a 'Some' then take 'Some' and if there are no 'Yes' or 'Some' responses then take a 'No'). I am trying to use an INDEX(COLLECT) formula but am only able to get one response and not the logic I described in the parentheses above. I am not sure if there is a way to add an IF(OR) statement or something similar but I am stumped. Below is the current formula I am using -

=IFERROR(INDEX(COLLECT({2024/25 Weekly Sports/Activity Attendance Range 1}, {2024/25 Weekly Sports/Activity Attendance Range 1}, OR(@cell = "Yes", @cell = "Some of them", @cell = "No"), {Weekly Attendance Info Range 2}, Student@row, {Weekly Attendance Info Range 3}, Month@row), 1), "No Data")

Screen Shot 2024-08-28 at 12.02.03 PM.png

Screen Shot 2024-08-28 at 12.02.17 PM.png

Screen Shot 2024-08-28 at 12.03.52 PM.png

Gillian C

Hi @Kishan Naik RA

It sounds like you want a nested if to give you the 3 different possibilities, however I'm struggling to work out what criteria you are needing to match and how your sheet is laid out. Is there a way for you to share a copy of your sheet with any sensitive information removed?

I could then have a look to see if I can help with the layout of your formula

Kishan Naik RA

Are you able to access the screenshots I uploaded? Essentially the criteria to match are the student name column and the month column. The weekly sheet (second screenshot) has hierarchy for each week with the same list of students and the first screenshot has a portion of week 1 uncollapsed to show the columns in the weekly sheet. The formula I shared above is in the sheet in the third screenshot and is trying to pull info from the coach info columns related to a specific student and month (four cells per student per month).

My issue is the formula is only pulling the first coach response it finds instead of reading all four weeks and picking the highest level response (Yes, Some of them, None of them). Let me know if that is helpful!

Gillian C

Hi @Kishan Naik RA

Thanks for the clarification, I understand better now. In order to look at all 4 weeks not just the first instance you will need to create a column that will show the instance of the student name and month. You will have potentially up to 5 entries per month. I have a similar formula that I'll pull out tomorrow for you and then using that column as a helper column I should be able to put together a nested if that will do what you have described above. Give me some time tomorrow and I'll get back to you on it.

Anything in the meantime just @ me so that I know you have messaged.

Kind Regards

Gillian

Kishan Naik RA

@Gillian C

I am glad the clarification was helpful! Take all the time you need to pull the formula, thanks so much for the help!

Gillian C

Hi @Kishan Naik RA

Apologies for not replying I've been off work ill for the past week and only just back in.

Did you manage to get a solution to your problem yet?

The formula for the helper column that I was suggesting is

=COUNTIFS([Student ID]$1:[Student ID]@row, [Student ID]@row, Month$1:Month@row, Month@row)

Please note this is a cell formula not column formula so it is counting how many instances of the above combination that you have, and this will change as you work down the month. This basically gives you a 1 for the first instance then 2 at the next and so on within the month.

You also need Helper 2, which is the column formula equivalent of the cell formula

=COUNTIFS([Student ID]:[Student ID], [Student ID]@row, Month:Month, Month@row)

This is stating how many instances of the Student ID appear in the month…this will always be the total number of instances from the Helper column for the Student ID in Month.

The formula below summarizes for each Month if a Students attendance should be listed as Yes, Some or No based on the inputs from each week.

This is a very large formula, but to explain what it is doing so you can interpret it into your own sheet it is basically asking:

If [Helper 2] (the number of instances in month of the Student ID and Month combination) = 1,

then IF the value of the instance = Yes, return Yes, if it is = Some return Some, if it is blank return No.

If [Helper 2] (the number of instances in month of the Student ID and Month combination) = 2,

then IF the value of the first instance in month or 2nd instance in month = Yes, return "Yes",

IF the value of the first instance in month or 2nd instance in month = Some, return "Some", "No".

If [Helper 2] (the number of instances in month of the Student ID and Month combination) = 3,

then IF the value of the first instance in month or 2nd or 3rd instance in month = Yes, return "Yes",

IF the value of the first instance in month or 2nd or 3rd instance in month = Some, return "Some", "No".

If [Helper 2] (the number of instances in month of the Student ID and Month combination) = 4,

then IF the value of the first instance in month or 2nd or 3rd or 4th instance in month = Yes, return "Yes",

IF the value of the first instance in month or 2nd or 3rd or 4th instance in month = Some, return "Some", "No".

If [Helper 2] (the number of instances in month of the Student ID and Month combination) = 5,

then IF the value of the first instance in month or 2nd or 3rd or 4th or 5th instance in month = Yes, return "Yes",

IF the value of the first instance in month or 2nd or 3rd or 4th or 5th instance in month = Some, return "Some", "No".

The formula is noted below but it is important to note with a formula this size you may run out of characters if your column names etc are too long. For this reason I would suggest shortening the cross sheet references that you have used.

=IF(INDEX(COLLECT({Helper 2}, {Student ID}, [Student ID]@row, {Month}, Month@row), 1) = 1, IF(INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 1), 1) = "Yes", "Yes", IF(INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 1), 1) = "Some", "Some", "No")), IF(INDEX(COLLECT({Helper 2}, {Student ID}, [Student ID]@row, {Month}, Month@row), 1) = 2, IF(OR(INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 1), 1) = "Yes", INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 2), 1) = "Yes"), "Yes", IF(OR(INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 1), 1) = "Some", INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 2), 1) = "Some"), "Some", "No")), IF(INDEX(COLLECT({Helper 2}, {Student ID}, [Student ID]@row, {Month}, Month@row), 1) = 3, IF(OR(INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 1), 1) = "Yes", INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 2), 1) = "Yes", INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 3), 1) = "Yes"), "Yes", IF(OR(INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 1), 1) = "Some", INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 2), 1) = "Some", INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 3), 1) = "Some"), "Some", "No")), IF(INDEX(COLLECT({Helper 2}, {Student ID}, [Student ID]@row, {Month}, Month@row), 1) = 4, IF(OR(INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 1), 1) = "Yes", INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 2), 1) = "Yes", INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 3), 1) = "Yes", INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 4), 1) = "Yes"), "Yes", IF(OR(INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 1), 1) = "Some", INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 2), 1) = "Some", INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 3), 1) = "Some", INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 4), 1) = "Some"), "Some", "No")), IF(INDEX(COLLECT({Helper 2}, {Student ID}, [Student ID]@row, {Month}, Month@row), 1) = 5, IF(OR(INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 1), 1) = "Yes", INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 2), 1) = "Yes", INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 3), 1) = "Yes", INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 4), 1) = "Yes", INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 5), 1) = "Yes"), "Yes", IF(OR(INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 1), 1) = "Some", INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 2), 1) = "Some", INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 3), 1) = "Some", INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 4), 1) = "Some", INDEX(COLLECT({Attendance}, {Student ID}, [Student ID]@row, {Month}, Month@row, {Helper}, 5), 1) = "Some"), "Some", "No")), "")))))

I appreciate this is very long, and again sorry for taking ages to get back to you but I just haven't been at all well.

…Also no problem if you already have a solution :)

Kishan Naik RA

@Gillian C no worries at all, in fact i have also been out of the office and just got back so I haven't gotten to this until now. Thank you for the formula, while it is long your explanation makes sense and it sounds like a formula like this is what I need. However I am having an issue currently using this formula, when I copy and paste it I am getting an #INVALIDREF error. I am wondering if the formula isn't referencing the columns that it is supposed to read but do you have an other troubleshooting advice?

Gillian C

Hi @Kishan Naik RA

Yes it sounds like you need to check the column references are correct, especially if you have cross sheet references, just check they are pointing to the correct column.

Let me know how you get on :)