+ Reply to Thread
Results 1 to 4 of 4

Countifs not counting duplicates

  1. #1
    Registered User
    Join Date
    11-28-2017
    Location
    Norway
    MS-Off Ver
    2016
    Posts
    9

    Countifs not counting duplicates

    Hi everybody!
    I am working with an excel sheet where I need to count several values if they are fulfilling different criteria. As for now I believe my formula is correct, but it does not count all values and it seems like this is because of some of the values are exactly the same for different persons.
    The formula looks like this:
    =SUM(COUNTIFS($O$84:$O$593;{"TS-PSF";"TS- PSF"};$M$84:$M$593;{"ACTIVE";"SICK"}))
    There is supposed to be 15 that fulfills these criteria, but it only counts 13.

    I would appreciate all help! Thank you in advance.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Countifs not counting duplicates

    When you have 2 lists as criteria in a formula like this one must be a "row" and one a "column" so you need to transpose one, e.g. this will work

    =SUM(COUNTIFS($O$84:$O$593;TRANSPOSE({"TS-PSF";"TS- PSF"});$M$84:$M$593;{"ACTIVE";"SICK"}))

    Confirm with CTRL+SHIFT+ENTER

    .....but better to just change separator. I don't know what's correct for Norway but if you put the above in a cell, then highlight just the ....

    TRANSPOSE({"TS-PSF";"TS- PSF"})

    ...then press F9 key you will see how it should look
    Audere est facere

  3. #3
    Registered User
    Join Date
    11-28-2017
    Location
    Norway
    MS-Off Ver
    2016
    Posts
    9

    Re: Countifs not counting duplicates

    Thank you for your reply, but when i try with TRANSPOSE it just gives me the error: #VALUE!
    Do you have any suggestions to why this happens?

  4. #4
    Registered User
    Join Date
    11-28-2017
    Location
    Norway
    MS-Off Ver
    2016
    Posts
    9

    Re: Countifs not counting duplicates

    I tried again with the method you suggested daddylonglegs, and it worked!!! Thank you so much for the help! :-)

+ 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 - Counting in order in one column, Counting only specific cells In another
    By kslattery in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-25-2017, 01:17 PM
  2. How can I run CountIfs + CountIfs without double counting?
    By hiitsjessie in forum Excel General
    Replies: 1
    Last Post: 02-06-2017, 04:49 PM
  3. [SOLVED] Countifs by counting duplicates as one in given date range
    By Sekars in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-12-2016, 06:53 AM
  4. Counting non-duplicates using countifs() with other criteria - how?
    By tforan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-19-2014, 03:31 AM
  5. [SOLVED] COUNTIFS without duplicates
    By amphinomos in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-12-2014, 11:08 AM
  6. [SOLVED] Counting Matching values in two separate ranges without counting duplicates
    By Rhall6310 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-01-2014, 03:35 PM
  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