+ Reply to Thread
Results 1 to 6 of 6

Countifs with duplicates

  1. #1
    Registered User
    Join Date
    11-13-2021
    Location
    Lisbon
    MS-Off Ver
    Microsoft 365
    Posts
    4

    Question Countifs with duplicates

    Hey everyone,

    I have sales data with each line including the date of sale and the Customer name. Now, I want to insert a formula in order to count how many different customers we had in a single month (The last complete one). In the attached file, the result for January 2023 (Current months = February) should be six (Customers A, B, C, D, E, F).

    Going into detail on the file:
    - The cell I want the solution in is the 'highlighted in red' one in the tab "distribution"
    - The current formula counts how many different customers bought in total, but it's not linked to the date yet - this is my problem

    Struggling to find a solution for this. Can someone help me out?

    Best
    MauWi
    Attached Files Attached Files
    Last edited by MauWi; 11-08-2022 at 08:30 AM. Reason: Solved

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Countifs with duplicates

    Hi,
    one way:

    in M =UNIQUE(DATE(YEAR(C3:C87),MONTH(C3:C87),1))
    in N =COUNTA(UNIQUE(FILTER($G$3:$G$87,DATE(YEAR($C$3:$C$87),MONTH($C$3:$C$87),1)=M3)))
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Countifs with duplicates

    OR if you want the result in 'Distribution' tab:

    C8 =COUNTA((UNIQUE(FILTER('Actual Sales'!$G$3:$G$87,(MONTH('Actual Sales'!$C$3:$C$87)=MONTH(B6))*(YEAR('Actual Sales'!$C$3:$C$87)=YEAR(B6))))))

  4. #4
    Registered User
    Join Date
    11-13-2021
    Location
    Lisbon
    MS-Off Ver
    Microsoft 365
    Posts
    4

    Re: Countifs with duplicates

    You are my hero belinda200. Thanks especially for the second answer.
    Have a great week!

  5. #5
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,341

    Re: Countifs with duplicates

    Or try in C8:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Countifs with duplicates

    yay I'm a hero
    You're welcome MauWi

+ 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. Countifs not counting duplicates
    By Huli17 in forum Excel General
    Replies: 3
    Last Post: 01-19-2018, 05:09 AM
  2. [SOLVED] Countifs if duplicates
    By Noah101 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-11-2017, 09:41 PM
  3. [SOLVED] CountIfs(?) Ignoring Duplicates
    By thesonofdarwin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-02-2017, 12:31 AM
  4. Countifs function without duplicates
    By darkhangelsk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-10-2014, 04:39 PM
  5. [SOLVED] COUNTIFS without duplicates
    By amphinomos in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-12-2014, 11:08 AM
  6. COUNTIFS with no duplicates
    By dasseya1 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 08-23-2013, 04:09 AM
  7. COUNTIFS and Duplicates
    By connorwfarrell in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-05-2012, 04:17 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