Please help.
I have a big spreadsheet with thousands of rows. I need formula (or it can be macro if it is easier) for "total credits for every student" column that sums up credits from column "credits" for every student from the "name" column.
Some students took same classes more than once (for example, student 2 took class 102 three times). But I need to sum up credits for these classes only once. Also, in case, if student did not pass the class ("NC" in the "gr" column), credits for this class should not be counted at all. I have put "0" for those classes in the "credits" column. Credits for every class in column “credits” I can found by lookup formula (or index match formula) from another table; however, maybe all these things can be included in one formula in the “total credits for every student” column?
I have tried to use this formula (=SUMIFS(H:H,F:F,F2,E:E,"P"), however I do not know how to include here criteria for unique class number for every student. This means I don’t know how to sum up credits for duplicate classes with gr="P" only once.
So, main criteria are
Calculate total credits for every student.
Count duplicate classes only once.
If gr=NC, do not count this class for total credits at all.
please look attached file.formulas_.xlsx
Thank you.
Bookmarks