Count Array with multiple variables--maybe

E

excelnewbie12345

New member
Joined
Jun 25, 2019
Messages
8
Reaction score
0
Points
0
Excel Version(s)
16.24
Two issues: I am trying to identify the number of students by class level (there are several levels); of a specific grade, of a certain demographic (demographics is represented by a number). Each student is represented by a student ID--However I do not want to count the student twice..I have no idea how to do this.
This is the formula I've been using, =IFERROR(ROWS(UNIQUE(FILTER('FinalGrades19-20'!$C$2:$C$16777,('FinalGrades19-20'!$V$2:$V$16777="Level 1")*('FinalGrades19-20'!$T$2:$T$16777=9)))), 0)
Also, if I try to add another variable (demographic #) it errors. I'm not sure this is the most efficient formula. I'm new to excel. Confused. Frustrated.I've attached an example.

View attachment Example.xlsxView attachment Example.xlsx
 
Your attached file contains little relevant data. There are links to a file we have no access to.
 
In the attached on sheet Sheet1, there are 2 pivot tables, one at cell A14 which reads Count of SNumber.
This is a regular pivot table and gives the wrong answers.

The second pivot table at cell A39 which reads Distinct Count of SNumber, I think gives you what you're looking for.
This is a pivot table which during creation I ticked the tick box Add this data to the Data Model:
2020-12-23_203829.png
This gives us one more way to summarise the data; a distinct count:
2020-12-23_203952.png
 

Attachments

  • ExcelGuru10974Example.xlsx
    415.1 KB · Views: 12
Thank you for your assistance--but I've run into another issue. I am using a mac which does not have the option for distinct count--I've searched MS and there is no add-in for this feature. What to do?? Cry? Throw my MAC through a window?? I have another inquiry as well, but I will post another thread. I may here your eyes roll back for my next question. I REALLY wish there was a book for illogical people trying to use excel.
 
In the attached is a pivot at cell A11 of Sheet1 which is a Pivot of a Pivot based loosely on an idea here:
https://www.contextures.com/pivottablecountunique.html#pivot
That pivot table is a pivot table based on the Pivot table on the new sheet IntermediatePivot.
It gives the same result as the Distinct count in the Data Model pivot table at cell A39.
 

Attachments

  • ExcelGuru10974Examplev2.xlsx
    418.6 KB · Views: 9
You must log in or register to reply here.
Back
Top