+ Reply to Thread
Results 1 to 18 of 18

How to count unique text & number to have same function with distinct count in office 2013

  1. #1
    Registered User
    Join Date
    03-31-2016
    Location
    hong kong
    MS-Off Ver
    2013
    Posts
    10

    Post How to count unique text & number to have same function with distinct count in office 2013

    Hi Everyone,

    I am trying to find way to figure out total styles count per each category and each season. The data is in different tab. Somehow I couldn't attach the file here.
    I am using office 2013 and know that there is distinct count in pivot table in office 2013 that can help my issue. However my data base will keep changing and this will require me to re-pivot the data.
    Therefore I prefer to use formula for my report so that any changes can be automatically captured since the file will cover large data.
    For example below is data that is saved in “data tab” meantime I want to create summary on “answer” tab.

    Style Season Category
    FM100 Fall 16 adult
    FM200 Fall 16 adult
    FM300 Fall 16 adult
    FM400 Fall 15 adult
    FM500 Fall 16 kids
    FM600 Fall 15 adult
    FM700 Fall 15 kids
    FM200 Fall 16 adult

    Expectedly the summary would go like this in “answer” tab.

    Category Fall 15 F16
    adult 2 3
    kids 1 1

    I am not familiar with if frequency or sum product and I tried to create the formula :
    =SUMPRODUCT(--(FREQUENCY(MATCH(data!C2:C11,data!C2:C11,0),ROW(data!C2:C11)-ROW(data!B2)+1)>0))
    The above formula only can count total styles and I have no idea on how to customize the formula so that I can get the figure as in my summary table. Pls let me know if this is really workable with formula or there is no way except using pivot table with distinct count function. If excel can help, pls share your expertise .

    Thanks in advance

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,725

    Re: How to count unique text & number to have same function with distinct count in office

    To attach a sample workbook.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).
    Never use Merged Cells in Excel

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    29,500

    Re: How to count unique text & number to have same function with distinct count in office

    With table as below (in F1:H3 in the formula)

    Please Login or Register  to view this content.
    in G2

    =COUNTIFS($B$2:$B$9,G$1,$C$2:$C$9,$F2)

    Copy across and down

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to count unique text & number to have same function with distinct count in office

    Is Fall 16 in a single cell or is Fall in its own cell and 16 in its own cell?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    29,500

    Re: How to count unique text & number to have same function with distinct count in office

    I have assumed "Fall 16" (Season) refers to Fall in 2016 (??) so one column.

  6. #6
    Registered User
    Join Date
    03-31-2016
    Location
    hong kong
    MS-Off Ver
    2013
    Posts
    10

    Re: How to count unique text & number to have same function with distinct count in office

    Hi again,

    Thanks for looking into my case.
    This time I could upload the sample workbook.
    Sorry that the data became messy, it was not like when I previewed them.
    Actually there are 3 columns : Style (indicated with prefix FM), season (Fall 16 & 15) and Category (adult & kids)

    Many Thanks
    Attached Files Attached Files
    Last edited by FM_mine; 04-06-2016 at 10:28 PM. Reason: revised attachement

  7. #7
    Registered User
    Join Date
    03-31-2016
    Location
    hong kong
    MS-Off Ver
    2013
    Posts
    10

    Re: How to count unique text & number to have same function with distinct count in office

    Correctm Fall 16 is season and below F16 should be Fall 16, they are under one column.

  8. #8
    Registered User
    Join Date
    03-31-2016
    Location
    hong kong
    MS-Off Ver
    2013
    Posts
    10

    Re: How to count unique text & number to have same function with distinct count in office

    Quote Originally Posted by Tony Valko View Post
    Is Fall 16 in a single cell or is Fall in its own cell and 16 in its own cell?
    Fall 16 is in one cell and under Season column, thanks

  9. #9
    Registered User
    Join Date
    03-31-2016
    Location
    hong kong
    MS-Off Ver
    2013
    Posts
    10

    Re: How to count unique text & number to have same function with distinct count in office

    Quote Originally Posted by zbor View Post
    To attach a sample workbook.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).
    Thanks zbor, it worked
    Last time I clicked the paperclip icon but it didn't work. It only left me with white box and couldn't do anything with it.

  10. #10
    Registered User
    Join Date
    03-31-2016
    Location
    hong kong
    MS-Off Ver
    2013
    Posts
    10

    Re: How to count unique text & number to have same function with distinct count in office

    Quote Originally Posted by JohnTopley View Post
    With table as below (in F1:H3 in the formula)

    Please Login or Register  to view this content.
    in G2

    =COUNTIFS($B$2:$B$9,G$1,$C$2:$C$9,$F2)

    Copy across and down
    Thank you John, I tried the formula. For Fall 16 it should be 3 styles in for adult. The formula should not count the duplicate text/number and blank cell.

  11. #11
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: How to count unique text & number to have same function with distinct count in office

    In the answer sheet:

    B2: =SUM(SIGN(FREQUENCY(IF(Season=B$1,IF(Category=$A2,MATCH(Style,Style,0))),MATCH(Style,Style,0))))


    Array formula press Ctrl+Shift+Enter, not just Enter

    copy down and across

  12. #12
    Registered User
    Join Date
    03-31-2016
    Location
    hong kong
    MS-Off Ver
    2013
    Posts
    10

    Re: How to count unique text & number to have same function with distinct count in office

    Hello Teethless Mama....

    Awesomeee! it worked...so happyyy....
    but wondering when I add more styles in data sheet, the summary in answer does not change automatically...even after I pressed ctrl + shift + enter. is it possible to have the formula auto calculate once the data got changed?

    also when I applied the formula on my other file which has same concept, it didn't show me anything...is there anything I should do differently?

    bunch of thanks

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    29,500

    Re: How to count unique text & number to have same function with distinct count in office

    Season, Category and Style are named ranges and probably fixed ranges:

    Change "Refers to:" in Name Manager to =OFFSET(data!$A$2,,,COUNTA(data!$A:$A)-1) for Style

    Change Season & Category likewise.

    These ranges automatically expand/contract as data is added/deleted

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to count unique text & number to have same function with distinct count in office

    Quote Originally Posted by FM_mine View Post
    Fall 16 is in one cell and under Season column, thanks
    Create these dynamic named ranges...

    Name: Style
    Refers to: =data!$A$2:INDEX(data!$A:$A,MATCH("zzzzz",data!$A:$A))

    Name: Season
    Refers to: =data!$B$2:INDEX(data!$B:$B,MATCH("zzzzz",data!$B:$B))

    Name: Category
    Refers to: =data!$C$2:INDEX(data!$C:$C,MATCH("zzzzz",data!$C:$C))

    Then...

    Data Range
    A
    B
    C
    1
    Category
    Fall 15
    Fall 16
    2
    adult
    2
    3
    3
    kids
    1
    1


    This array formula** entered in B2:

    =SUM(IF(FREQUENCY(IF(Category=$A2,IF(Season=B$1,MATCH(Style,Style,0))),ROW(Style)-MIN(ROW(Style))+1),1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy across to C2 then down as needed.

  15. #15
    Registered User
    Join Date
    03-31-2016
    Location
    hong kong
    MS-Off Ver
    2013
    Posts
    10

    Re: How to count unique text & number to have same function with distinct count in office

    Quote Originally Posted by Tony Valko View Post
    Create these dynamic named ranges...

    Name: Style
    Refers to: =data!$A$2:INDEX(data!$A:$A,MATCH("zzzzz",data!$A:$A))

    Name: Season
    Refers to: =data!$B$2:INDEX(data!$B:$B,MATCH("zzzzz",data!$B:$B))

    Name: Category
    Refers to: =data!$C$2:INDEX(data!$C:$C,MATCH("zzzzz",data!$C:$C))

    Then...

    Data Range
    A
    B
    C
    1
    Category
    Fall 15
    Fall 16
    2
    adult
    2
    3
    3
    kids
    1
    1


    This array formula** entered in B2:

    =SUM(IF(FREQUENCY(IF(Category=$A2,IF(Season=B$1,MATCH(Style,Style,0))),ROW(Style)-MIN(ROW(Style))+1),1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy across to C2 then down as needed.
    Thanks Tony, I tried the formula with index and just added "if error" in case there is blank. Here is example for style : =IFERROR(data!$A$2:INDEX(data!$A:$A,MATCH("Style",data!$A:$A)),"")
    I did all for the rest, but somehow for category after row 6 the cell showed blank only. The rest looks fine. Do you have any idea why this can happen?
    For reference, I attached the file and I did your formula in "try formula" tab.
    Also not sure why there is always error pop up after I created this formula and press ctrl+shift+enter =SUM(IF(FREQUENCY(IF(Category=$A2,IF(Season=B$1,MATCH(Style,Style,0))),ROW(Style)-MIN(ROW(Style))+1),1))

    Gotta check with my IT team here

    Thank you so much
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    03-31-2016
    Location
    hong kong
    MS-Off Ver
    2013
    Posts
    10

    Re: How to count unique text & number to have same function with distinct count in office

    Quote Originally Posted by JohnTopley View Post
    Season, Category and Style are named ranges and probably fixed ranges:

    Change "Refers to:" in Name Manager to =OFFSET(data!$A$2,,,COUNTA(data!$A:$A)-1) for Style

    Change Season & Category likewise.

    These ranges automatically expand/contract as data is added/deleted
    Thank you John,
    I can do with this formula and added if error too just to anticipate blank cells.

  17. #17
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to count unique text & number to have same function with distinct count in office

    You didn't create the dynamic named ranges.

    Also, the formula you have doesn't look anything like the formula I suggested plus they're array formulas referencing entire columns which you should avoid.

  18. #18
    Registered User
    Join Date
    03-31-2016
    Location
    hong kong
    MS-Off Ver
    2013
    Posts
    10

    Re: How to count unique text & number to have same function with distinct count in office

    Sorry just come back Tony, let me double check ...
    I am just deepening my excel lately and not so many people I can rely on in my new workplace

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Repeat text count based on unique ID number
    By rn_ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-29-2015, 04:04 PM
  2. [SOLVED] Excel - Count distinct occurences and copy unique values
    By random0munky in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-26-2015, 10:41 PM
  3. [SOLVED] Formula to count the number of unique text values from a list.
    By PritishD in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-28-2015, 05:42 AM
  4. [SOLVED] Pivot Table that performs a distinct count in excel 2013
    By tdf2437 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 09-09-2014, 09:03 AM
  5. Replies: 5
    Last Post: 03-13-2012, 06:05 AM
  6. Count of distinct (unique) values by day
    By velorian in forum Excel General
    Replies: 7
    Last Post: 12-06-2011, 05:03 PM
  7. Pivot Tables-To Find Count of Unique(Distinct)User id within the same Journal Entry.
    By Deepthik in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-10-2010, 06:09 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1