How can I get a SUM COUNT based on an unique identifier that may appear in multiple sheets & omit duplicates from the count

[cvercrus]

Good morning! I have search everywhere and can’t seem to find a fit for my dilemma. I work for a college and currently creating the 2012-2013 Fact Book. I have 3 terms that drive my data analysis. I have created COUNTIFS with multiple criteria for each of the terms and the count for what I’m pulling works fine (term comparison). Now, I need to get the total count for all 3 terms.

Why not add the 3 term results, you may be asking? Well, in each term, a student who appeared in the Fall hopefully is a returning student and appears in the Spring and so on for Summer. Adding each term result would inflate the yearly number due to duplication of records if I just took the count from each term and added them together which would give me awesome numbers for yearly enrollment, but duplication of records in the process. I could very well copy and paste each of the terms into another worksheet and un-duplicate records, but I’m trying to write formulas that can do the work for me and simply change the Sheet name for the future.

WHAT I’M TRYING TO ACCOMPLISH:
I need to COUNT the number of student records for each term (3 spreadsheets of data) where the STUDID (Column A for each sheet) is the unique identifier that links the record and will drive the count as either a new student for a particular term or a duplicate (a returning student). It is important to emphasis that the STUDID was not needed in my COUNTIFS formulas when seeking data for my individual term results because the data was based on specific criteria within the worksheet (term).

*** How can I go about setting up a formula (please not VBA code) that will look up COLUMN A (STUDID) for each worksheet (Fall, Spring, Summer) to determine whether it is a new STUDID or a duplicate and based on the following criteria return a SUM COUNT that will be UNDUPLICATED (counting the record only once from all 3 worksheets). Below is my COUNTIFS formula that extracts the count from an individual term and the criteria.

This criterion looks up students who are Non-Hispanic + Race for a particular Program of Study and provides a SUM COUNT:
=COUNTIFS('F2012'!$N$2:$N$5000,"ACCT",'F2012'!$AZ$2:$AZ$5000,"NHS",'F2012'!$BA$2:$BA$5000,"AN")+COUNTIFS('F2012'!$N$2:$N$5000,"ACCT",'F2012'!$BA$2:BA$5000,"AN",'F2012'!$AZ$2:$AZ$5000,"=")

F2012 = worksheet name (represents Fall records) --- there is a SP2013 = Spring and SU2013 = Summer
!$N$2:$N$5000 = Program of Study (in this case, Accounting = “ACCT”)
$AZ$2:$AZ$5000 = Non-Hispanic (value entered = “NHS”)
$AZ$2:$AZ$5000 = Non-Hispanic (blank; no value entered = “=”)
!$BA$2:$BA$5000 = Race (in this case, American Indian or Alaskan Native = “AN”)

For this Program of Study (ACCT) I had a total of 2 students. However, if I add the totals for each term (Fall, Spring, Summer), I get 4. The students were enrolled in Fall and Spring, but did not enroll for Summer. Had the 2 students not taken off for the Summer, the Total for Program of Study (ACCT) would have been 6, but I need the value of 2 because I need unduplicated headcount.

Is there anyone out there than can solve this? Your assistance in solving this dilemma is most appreciated. Cris

 

[Jerry Sullivan]

Hi Cris and Welcome to the Board,

I think this could be done more simply with an SQL query than a formula-based solution.

Here's one query that would return the Number of Students to a Worksheet Cell.

SELECT COUNT(*)  AS NumberOfStudents
FROM(
SELECT DISTINCT STUDID
FROM(
SELECT T1.STUDID
FROM dataF2012 T1
WHERE (T1.[Program of Study]='ACCT') AND (T1.[Non-Hispanic]='NHS') AND (T1.Race='AN')
UNION ALL
SELECT T2.STUDID
FROM dataSP2013 T2
WHERE (T2.[Program of Study]='ACCT') AND (T2.[Non-Hispanic]='NHS') AND (T2.Race='AN')
UNION ALL
SELECT T3.STUDID
FROM dataSU2013 T3
WHERE (T3.[Program of Study]='ACCT') AND (T3.[Non-Hispanic]='NHS') AND (T3.Race='AN')
) )

This is relatively easy to setup using MS Query and it doesn't require any VBA.
Just ask if you want some help implementing this in your workbook.