# Thread: Individual To Subject Count (Calc Help)

1. Hi,

I need help with a calculation to calculate how many "individuals" attended which courses. I have attached a spreadsheet with the sample data. TIA for your help.

2. You can do this in two steps: Advanced Filter to extract unique records, and a pivot table to calculate the counts. See this recent topic.

3. That will work on the attached (modified) file, but the prob is that the records on the original source file have additional fields of data that will not allow an advanced unique filter. I was thinking in line of a formula (or array) formula to determine a count for unique person/subject count.

Any ideas?

4. You don't have to include all column headings in the Extract to range for Advanced Filter, you should only use those needed to get the unique records.

See the attached version: [attachment=86305:Subject Count.xls]

5. Thanks for the advice. I'll give it a go on the original.
Appreciate the help

6. Another way to do it is by creating an auxiliary column in the source table.
The formula in G2 is =--(SUMPRODUCT((A\$2:A2=A2)*(F\$2:F2=F2))<2), this can be filled down.
This returns 1 for the first occurrence of an Id-Number for a specific Subject Description, 0 otherwise.
You can then use SUMIF formulas or a pivot table to count the unique Id-Numbers for each Subject Description.
With SUMIF, the formula in J8 is =SUMIF(\$F\$2:\$F\$349,I8,\$G\$2:\$G\$349), this can be filled down.

See attached version: [attachment=86311:Subject Count2.xls]

7. Originally Posted by Rudi
........I was thinking in line of a formula (or array) formula to determine a count for unique person/subject count.
Another way, wihout using helper column,

Cell J8, enter the formula and copy down :

=SUMPRODUCT((F\$2:F\$349=I8)*(MATCH(F\$2:F\$349&A\$2:A\$ 349,F\$2:F\$349&A\$2:A\$349,)=ROW(F\$2:F\$349)-1))

Regards
Bosco

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•