+ Reply to Thread
Results 1 to 6 of 6

Count distinct values based on two criteria

  1. #1
    Forum Contributor
    Join Date
    02-14-2017
    Location
    India
    MS-Off Ver
    2013
    Posts
    128

    Count distinct values based on two criteria

    Hi All,

    I am in need of counting the values from two sheet. In first sheet I have a column with dates and in second sheet I have a column with date and another column has different values with duplicates. First criteria: date then second criteria: distinct values. Hope its clear If need i will attach the sample file. I need the count to be updated in first sheet. Thanks in advance.

    Sheet 1
    Date Agent Count
    16-04-2020 3 < --- Expected Output
    17-04-2020 2
    20-04-2020 1

    Sheet 2
    Date Agent Name
    16-04-2020 A
    16-04-2020 A
    16-04-2020 B
    16-04-2020 C
    17-04-2020 D
    17-04-2020 D
    20-04-2020 E
    Manikandan Arumugam
    Excel Learner

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Count distinct values based on two criteria

    Use the Countifs function. Here is a link to how it works.

    https://www.techonthenet.com/excel/f...s/countifs.php
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    02-14-2017
    Location
    India
    MS-Off Ver
    2013
    Posts
    128

    Re: Count distinct values based on two criteria

    Hi Alan,

    Thanks for your time. But am not sure what value will be. I have more than 400 rows with different values. it will be helpful if i get an alternate solution. Thanks again!

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Count distinct values based on two criteria

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Count distinct values based on two criteria

    A
    B
    C
    D
    1
    SHEET1
    2
    16-04-2020
    3
    3
    17-04-2020
    1
    4
    20-04-2020
    1




    A
    B
    C
    D
    1
    Date Agent NAME SHEET2
    2
    16-04-2020 A
    3
    16-04-2020 A
    4
    16-04-2020 B
    5
    16-04-2020 C
    6
    17-04-2020 D
    7
    17-04-2020 D
    8
    20-04-2020 E


    Sheet1

    B2=IF(Sheet1!A2<>"",SUM(IF(FREQUENCY(IF(Sheet2!$A$2:$A$100=Sheet1!$A2,IF(Sheet2!$A$2:$A$100<>"",MATCH(Sheet2!$A$2:$A$100&Sheet2!$B$2:$B$100,Sheet2!$A$2:$A$100&Sheet2!$B$2:$B$100,0))),ROW(Sheet2!$A$2:$A$100)-ROW(Sheet2!$A$2)+1),1)),"")

    Control+shift+enter

    copy down

  6. #6
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Count distinct values based on two criteria

    another alternative:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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 distinct values based on criteria (large data)
    By ifulao in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-02-2020, 05:31 AM
  2. Count Distinct Values With A Criteria In A Filtered List
    By JonnyBoy333 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-31-2019, 02:52 AM
  3. [SOLVED] Count of Distinct function/ Formula based on multiple criteria
    By naveeddil in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-08-2019, 06:34 PM
  4. Sum column and count distinct values in another based on multiple criteria
    By mike.greene in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-12-2015, 11:50 AM
  5. Count distinct values that correspond to a criteria
    By Eduard in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 04-22-2013, 01:03 PM
  6. Distinct count based on multiple criteria - am I on the right track?
    By thedunnyman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-26-2012, 07:36 AM
  7. Count distinct values with criteria
    By greencardioid in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-29-2008, 06:48 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