Individual To Subject Count (Calc Help)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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?

Reply | Quote

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:

Reply | Quote

……..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

Reply | Quote

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

Reply | Quote

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:

Reply | Quote