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

[MRabelo]

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.

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"]J
[/TD]
[/TR]
[TR]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]8212[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]8212[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]9771[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]7877[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]7877[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]7877[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]4[/TD]
[/TR]
</tbody>[/TABLE]


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

 

[Domenic]

Try...

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET($F$7:$F$50000,ROW($F$7:$F$50000)-ROW($F$7),0,1)),MATCH("~"&$F$7:$F$50000,$F$7:$F$50000&"",0)),ROW($F$7:$F$50000)-ROW($F$7)+1)>0,$J$7:$J$50000))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[MRabelo]

Hi Domenic,

Thank you for responding! The formula is returning a zero.

 

[Domenic]

Are the numbers in Column J true numerical values? As an example, to test whether J7 contains a true numerical value, the following formula should return TRUE...

=ISNUMBER(J7)

Are the numbers in Column true numerical values?

 

[MRabelo]

Hi Domenic,

They are numeric values. I'm enclosing a screenshot of the sample report to this reply because I was unable to upload the actual sample worksheet.

 

[Domenic]

I don't see a link to your screenshot. In any case, here's a link to a sample workbook...

https://onedrive.live.com/redir?resid=41260C1C818928B2!650&authkey=!AG8FuS1y9qC6_bs&ithint=file,xlsx

Does this help?

 

[MRabelo]

Hi Domenic,

This is exactly what I was looking for! Thank you so much!!!

As a follow up question, how would I add parameters to the unique value count formula below? Column E indicates whether a student is a Graduate (GR), Undergrad Specialty (US) or Undergraduate (UG) student. Column X contains the student ID# (repeated multiple times). Is there a way to customize the formula to count unique values from X (Student ID) based on a specific student level from E (i.e. GR)?

*Returns unique student ID count from column X*
{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET($X$7:$X$50000,ROW($X$7:$X$50000)-ROW(X$7),0,1)),X$7:X$50000),IF(SUBTOTAL(3,OFFSET($X$7:$X$50000,ROW($X$7:$X$50000)-ROW(X$7),0,1)),$X$7:$X$50000))>0,1))}

 

[Domenic]

Try...

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET($X$7:$X$50000,ROW($X$7:$X$50000)-ROW(X$7),0,1)),IF($E$7:$E$50000="GR",MATCH("~"&X$7:X$50000,X$7:X$50000&"",0))),ROW($X$7:$X$50000)-ROW(X$7)+1)>0,1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[MRabelo]

Thanks Domenic! This is really helpful!!!!