Feb 12 2024 06:02 AM - edited Feb 12 2024 06:02 AM
Hello everyone,
I have a table called "Abilities". It names abilities and the group they belong to:
Further I have table called "Students"
It has a column for the Student and one column for each ability from the "Abilities" table with values between 1 and 3 representing their skill in said ability.
Now I want to calculate the sum of a students skill in an ability group in a new table called "Sum"
For example:
In the table "Sum" we want to find out the value for B2 (The Sum of Adams Skill for every Ability in Group 1.
In the table "Sum" the Cell B1 has the value 1. It represents group 1. A2 has the value Adam, representing the Student.
In the table "Abilities" values that match with Group 1 are See, Hear & Feel
In the table "Students" when we match Adam with the See, Hear & Feel we should get the values 1,3 & 3
Therefore the value for B2 should be 7.
Can you please help me solve this? Help would be much appreciated.
Feb 12 2024 06:38 AM - edited Feb 12 2024 06:45 AM
Solution@ellaumbrella Do you have Excel for MS365? If so, give this formula a try in cell B2 of your summary table:
=SUM(FILTER(FILTER(Students, ISNUMBER(XMATCH(Students[#Headers], TOCOL(IFS(Abilities[Group]=VALUE(B$1), Abilities[Ability]), 2)))), Students[Student]=[@Student]))
- OR -
=SUM(FILTER(FILTER(Students, ISNUMBER(XMATCH(Students[#Headers], FILTER(Abilities[Ability], Abilities[Group]=VALUE(B$1))))), Students[Student]=[@Student]))
Please see the attached workbooks, if needed...
Feb 12 2024 07:31 AM
Feb 12 2024 09:31 AM
@ellaumbrella You're welcome! Yes, it's a bit complicated due to the crosstab setup of your data input table. To analyze the formula, we can work backwards from the inside out and separate each step using the LET function:
=LET(
groupItems, FILTER(Abilities[Ability], Abilities[Group]=VALUE(B$1)),
includeCols, ISNUMBER(XMATCH(Students[#Headers], groupItems)),
groupData, FILTER(Students, includeCols),
studentRow, FILTER(groupData, Students[Student]=[@Student]),
SUM(studentRow)
)
Having said that, there are other methods you could also try. Power Query, for example, can be used to "unpivot" the Students table and join it to the Abilities table, then output the results to a pivot table. Another option would be to use subtotal columns in your Students table, then "group" the columns so they can be expanded and collapsed to show/hide the data. I've attached a couple more workbooks, demonstrating these methods. In the GroupColumns file, click the +/- buttons (above the column headers) to expand/collapse the grouped columns (or click the [1] and [2] buttons on the left-hand side to expand/collapse all groupings at once). Cheers!
Feb 12 2024 06:38 AM - edited Feb 12 2024 06:45 AM
Solution@ellaumbrella Do you have Excel for MS365? If so, give this formula a try in cell B2 of your summary table:
=SUM(FILTER(FILTER(Students, ISNUMBER(XMATCH(Students[#Headers], TOCOL(IFS(Abilities[Group]=VALUE(B$1), Abilities[Ability]), 2)))), Students[Student]=[@Student]))
- OR -
=SUM(FILTER(FILTER(Students, ISNUMBER(XMATCH(Students[#Headers], FILTER(Abilities[Ability], Abilities[Group]=VALUE(B$1))))), Students[Student]=[@Student]))
Please see the attached workbooks, if needed...