Sum values in one column based on unique, filtered values in another

[m.rabelo]

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!!!

[navic]

Please help me!..........Any help would be greatly appreciated!!!

Example attach file a lot of help in understanding. Here's an idea if that's the result of the second worksheet (if I understand you well)
See attached file

[m.rabelo]

Hi Navic,

Thank you for responding! I tried incorporating the enclosed formula but it kept returning a zero.

[Hercules1946]

Hi Navic,

Thank you for responding! I tried incorporating the enclosed formula but it kept returning a zero.

Did you remember to press Control+Shift+Enter and not just Enter to complete the matrix formulae ?

[m.rabelo]

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.

[candybg]

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

[Hercules1946]

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.

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.

[m.rabelo]

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

[navic]

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)

=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)

I hope it's helpful as an idea

[m.rabelo]

Hi Navic,

This is really helpful! Thank you so much!