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

m.rabelo

New member
Joined
Jul 23, 2015
Messages
5
Reaction score
0
Points
0
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!!! :)
 
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
 

Attachments

  • rabelo-navic.xlsx
    10.7 KB · Views: 101
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
 
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.
 
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)

Code:
=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
 

Attachments

  • mrabelo-navic2.xlsx
    16.3 KB · Views: 89
Back
Top