sum up values in one column for every group based on other creteria
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
Last edited by Alex_738; 09-17-2013 at 12:48 PM.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By childothe1980s in forum Excel Programming / VBA / Macros
Last Post: 05-26-2013, 03:47 PM
By alyaahmed in forum Excel General
Last Post: 03-28-2013, 01:40 PM
By Sniper in forum Excel Programming / VBA / Macros
Last Post: 09-25-2012, 10:25 AM
By Bearded Dan in forum Excel General
Last Post: 11-14-2007, 05:15 PM
By Yo in forum Excel General
Last Post: 04-10-2006, 08:20 PM
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1