+ Reply to Thread
Results 1 to 2 of 2

Count unique values in a table that meet one or two criterias

  1. #1
    Registered User
    Join Date
    11-25-2019
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    4

    Count unique values in a table that meet one or two criterias

    Hi, i need some help with an array formel.
    I have to count unique values in a table that meet one or two criterias.
    I`ve added an excel file that explais it

    simplified table:
    a|b|c
    Id number |Week | Average deviation
    5 |40| |5 %
    5 |40 |0 %
    80 |40 |30 %
    10 |39 |15 %
    10 |40 |0 %
    5 |42 |20 %
    5 |43 |30 %
    80 |40 |28 %
    80 |40 |0 %
    10 |42 |0 %
    10 |43 |0 %
    10 |42 |15 %
    80 |40 |0 %
    5 |41 |0 %

    My rapport:
    G|H|I
    Id number | Total number of uniue weeks | total number of unique weeks with an deviation that is higher than 0%
    5 |"=sumuniquevalues in B:B that has their A value =5 | "=sumuniquevalues in B:B that has their A value =5 & their C value is >0
    10 |"=sumuniquevalues in B:B that has their A value =10 | "=sumuniquevalues in B:B that has their A value =10 & their C value is >0
    80 |"=sumuniquevalues in B:B that has their A value =80 | "=sumuniquevalues in B:B that has their A value =80 & their C value is >0




    Best regards
    Grenny
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,896

    Re: Count unique values in a table that meet one or two criterias

    In H5:
    =SUM(INDEX(($B$3:$B$16=G5)/COUNTIFS($B$3:$B$16,$B$3:$B$16&"",$C$3:$C$16,$C$3:$C$16&""),0))

    and in I5:
    =SUM(INDEX(($B$3:$B$16=G5)*($D$3:$D$16>0)/COUNTIFS($B$3:$B$16,$B$3:$B$16&"",$C$3:$C$16,$C$3:$C$16&"",$D$3:$D$16,$D$3:$D$16&""),0))

    You may need ; as the separator.
    Attached Files Attached Files
    Glenn



+ 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. Count Unique Values based on multiple criterias
    By Dahlia in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-10-2016, 09:10 AM
  2. Count Unique Values that Meet a Criteria
    By DD1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-09-2016, 11:23 PM
  3. Count Unique Values based on different Criterias
    By Morwick in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-03-2015, 04:49 PM
  4. [SOLVED] Count unique values based multiple criterias
    By Petter120 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-03-2014, 03:07 AM
  5. [SOLVED] How to count unique values using multiple criterias
    By Charmymay in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-07-2013, 11:27 AM
  6. Replies: 0
    Last Post: 05-05-2013, 05:47 AM
  7. Replies: 25
    Last Post: 05-31-2012, 08:03 PM

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