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

MRabelo

New Member
Joined
Jul 23, 2015
Messages
5
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

<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
Joined
Mar 10, 2004
Messages
19,045
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!
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,045
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
Joined
Jul 23, 2015
Messages
5
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
Joined
Jul 23, 2015
Messages
5
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
Joined
Mar 10, 2004
Messages
19,045
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!
 

Forum statistics

Threads
1,085,291
Messages
5,382,746
Members
401,802
Latest member
JodieInCanada

Some videos you may like

This Week's Hot Topics

Top