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

#### MRabelo

##### New Member

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

<tbody>
</tbody>

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

##### MrExcel MVP
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

##### New Member
Hi Domenic,

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

##### MrExcel MVP
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

##### New Member
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. #### MRabelo

##### New Member
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

##### MrExcel MVP
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

##### New Member
Thanks Domenic! This is really helpful!!!! 