+ Reply to Thread
Results 1 to 9 of 9

Adding multiple criteria to COUNTIFS (don't count duplicates)

  1. #1
    Registered User
    Join Date
    04-27-2017
    Location
    New York
    MS-Off Ver
    MS 365 v2211
    Posts
    85

    Adding multiple criteria to COUNTIFS (don't count duplicates)

    Hello, I'm looking for an explanation about how to add the second criteria to a COUNTIFS formula.

    I have an easy sample sheet attached, but I'd really like to gain an understanding of the principle, not only a solution to today's dilemma. The main premise is that I want to count the number of the businesses that are deemed "active" in column D, but some of these businesses are listed more than once in column A. I want to exclude these duplicates from the tally. (I understand that I could simply remove duplicate rows from column A, then do a regular COUNTIF on column D, but the real sheet includes additional data that must be salvaged).

    Any pointers on the failed attempted formulas in the sheet would be appreciated.

    Thank you,
    Attached Files Attached Files

  2. #2
    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
    28,848

    Re: Adding multiple criteria to COUNTIFS (don't count duplicates)

    I would think the UNIQUE function comes into play .... but as don't have 365 .....
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,229

    Re: Adding multiple criteria to COUNTIFS (don't count duplicates)

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

  4. #4
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,932

    Re: Adding multiple criteria to COUNTIFS (don't count duplicates)

    =ROWS(UNIQUE(FILTER(A2:A14,D2:D14="Active")))

  5. #5
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,938

    Re: Adding multiple criteria to COUNTIFS (don't count duplicates)

    All in one go

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,698

    Re: Adding multiple criteria to COUNTIFS (don't count duplicates)

    Try.
    In K2

    =SUMPRODUCT(1*(UNIQUE(A2:A14)<>""))

    In K3

    =SUMPRODUCT(1*(UNIQUE(FILTER(A2:A14,D2:D14="Active"))<>""))

    In K4

    =SUMPRODUCT(1*(UNIQUE(FILTER(A2:A14,D2:D14="Inactive"))<>""))
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  7. #7
    Registered User
    Join Date
    04-27-2017
    Location
    New York
    MS-Off Ver
    MS 365 v2211
    Posts
    85

    Re: Adding multiple criteria to COUNTIFS (don't count duplicates)

    Thanks, everyone. Lots of solutions do the trick here. I spent some time studying the UNIQUE formula. It was my first time using it.

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,229

    Re: Adding multiple criteria to COUNTIFS (don't count duplicates)

    Glad to help & thanks for the feedback

  9. #9
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,932

    Re: Adding multiple criteria to COUNTIFS (don't count duplicates)

    Same here.

+ 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. [SOLVED] Countifs formula to count unique text with multiple criteria
    By majana54 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-04-2022, 08:20 PM
  2. Countifs - Is there an easier way to count up data for multiple criteria?
    By Smurlos in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-30-2019, 07:16 AM
  3. Replies: 6
    Last Post: 12-14-2016, 12:36 PM
  4. Countifs with no duplicates and multiple criteria
    By tony75 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-06-2016, 10:10 AM
  5. Replies: 6
    Last Post: 10-21-2015, 09:44 AM
  6. Replies: 5
    Last Post: 05-04-2014, 10:56 AM
  7. [SOLVED] Need to use either COUNTIFS or SUMPRODUCT to count rows based on multiple criteria
    By erabinov in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-15-2013, 03:15 PM

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