Please help me!
I'm trying to sum filtered values from column J (course credits) based on unique values from column F (unique course ID).
The desired result would return a count of 3 unique values (courses) for column F and a subtotal of 10 (credits) for column J.
A
B
C
D
E
F
G
H
I
J
- - - - - 8212 - - - 3 - - - - - 8212 - - - 3 - - - - - 9771 - - - 3 - - - - - 7877 - - - 4 - - - - - 7877 - - - 4 - - - - - 7877 - - - 4
The formula listed below returns the unique count for column F, but I don't know how to add a parameter that would include the sum J based on F.
{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET($F$7:$F$50000,ROW($F$7:$F$50000)-ROW(F$7),0,1)),F$7:F$50000),IF(SUBTOTAL(3,OFFSET($F$7:$F$50000,ROW($F$7:$F$50000)-ROW(F$7),0,1)),$F$7:$F$50000))>0,1))}
Any help would be greatly appreciated!!!![]()
My personal Web Excel Tutorials
Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
Also for a decimal number I use a comma (,) you may need to use point (.) instead of
Hi Navic,
Thank you for responding! I tried incorporating the enclosed formula but it kept returning a zero.![]()
Yes, but it still returned a zero. What I am trying to do is sum the amount of credits each course is generating without double or triple counting them. In my sample table, column F contains the unique course reference number and column J contains the amount of credits for the course. Columns A through E contains information such as the course term, college, department, student etc. When I filter according to either college, term or department, I want to be able to see the total amount of credits according to specific parameters.
I would think you should be able to use a pivot table to pull any data/totals you need. Slicers would do your "filtering".
Hope this helps!
cbug
Hello M.rabelo
After Id looked at your information, I could see that your familiar with matrix formulae, so I apologise for that.
I've looked at Navics solution, and his illustration appears to be doing what you require. I would check carefully that you have entered everything correctly in the formula. Sometimes a missing or misplaced bracket can cause an error that won't be picked up by the editor.
If that doesn't solve it, then I suggest you post an example spreadsheet with a small amount of data, including your formula(e) returning zero, and Im sure we can figure it out.
Hello Hercules,
I'm attaching a sample spreadsheet that better illustrates what I'm trying to do.
Thank you so much for looking into this!Formula Sample - 07-29-15.xlsx
If the situation permits you add a the extra column
Using SUMIF function, count how many of conditions in pairs
You pay attention to the formula in cell D27, connected with the cell B27 where is your text (as condition)
I hope it's helpful as an ideaCode:=SUMIF($E$6:$K$23;"GR";$K$6:$K$23) or =SUMIF($E$6:$K$23;MID(B27;SEARCH("(";B27;1)+1;2);$K$6:$K$23)
My personal Web Excel Tutorials
Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
Also for a decimal number I use a comma (,) you may need to use point (.) instead of
Hi Navic,
This is really helpful! Thank you so much!
Bookmarks