Results 1 to 8 of 8

Thread: Count Array with multiple variables--maybe

  1. #1
    Seeker excelnewbie12345's Avatar
    Join Date
    Jun 2019
    Posts
    8
    Articles
    0
    Excel Version
    16.24

    Count Array with multiple variables--maybe



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

    Example.xlsxExample.xlsx

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,891
    Articles
    0
    Excel Version
    365
    Your attached file contains little relevant data. There are links to a file we have no access to.

  3. #3
    Seeker excelnewbie12345's Avatar
    Join Date
    Jun 2019
    Posts
    8
    Articles
    0
    Excel Version
    16.24
    Example.xlsx

    That would help...

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,891
    Articles
    0
    Excel Version
    365
    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:
    Click image for larger version. 

Name:	2020-12-23_203829.png 
Views:	8 
Size:	10.7 KB 
ID:	10253
    This gives us one more way to summarise the data; a distinct count:
    Click image for larger version. 

Name:	2020-12-23_203952.png 
Views:	10 
Size:	8.8 KB 
ID:	10254
    Attached Files Attached Files

  5. #5
    Seeker excelnewbie12345's Avatar
    Join Date
    Jun 2019
    Posts
    8
    Articles
    0
    Excel Version
    16.24
    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.

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,891
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by excelnewbie12345 View Post
    What to do??
    Throw your MAC through a window.

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,891
    Articles
    0
    Excel Version
    365
    What version of Excel? Maybe it is available (It's Get & Transform Data you need):
    https://techcommunity.microsoft.com/...-1/ba-p/876840

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,891
    Articles
    0
    Excel Version
    365
    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/pivottab...que.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.
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

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