+ Reply to Thread
Results 1 to 7 of 7

Countifs unique values based on two criteria

  1. #1
    Forum Contributor
    Join Date
    12-06-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    139

    Countifs unique values based on two criteria

    All

    My first post....

    I have a data set that contains various records against unique identifier. I need to be able to return a vaule of "Y" instead of the count - indicating that the unqiue identifer has at least one record with a specified value.

    I have tried countifs, countifs & frequency but can't seem to figure it out.

    The countifs formula is =COUNTIFS(CASS_Staff_Submission!$D:$D,Sheet2!$D:$D,CASS_Staff_Submission!$K:$K,Sheet2!CH$1) and currently returns a count of 3 - I need to have a "Y" returned..

    I have attached a very small example

    any thoughts appreciated

    Shaz0503
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,450

    Re: Countifs unique values based on two criteria

    Because you have links in your spreadsheet, I am unable to verify this, but I wrapped your formula in an =If formula
    ie. =if((Yourformula) >0,"Y",0)

  3. #3
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,842

    Re: Countifs unique values based on two criteria

    Try changing it to:

    =IF(COUNTIFS(CASS_Staff_Submission!$D:$D,Sheet2!$D:$D,CASS_Staff_Submission!$K:$K,Sheet2!CH$1)>0,"Y",0)

    - Moo

    * Alan got to it first on this one. Who's fast now?

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: Countifs unique values based on two criteria

    how about including if statement

    =if(countformula)>0,"Y","")
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  5. #5
    Forum Contributor
    Join Date
    12-06-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: Countifs unique values based on two criteria

    Quote Originally Posted by alansidman View Post
    Because you have links in your spreadsheet, I am unable to verify this, but I wrapped your formula in an =If formula
    ie. =if((Yourformula) >0,"Y",0)
    Alan

    ...tis always o easy when you know the answer.... thanks heaps

    shaz0503

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,450

    Re: Countifs unique values based on two criteria

    @Moo
    No twins in my family

    @Shaz0503: you are welcome.

  7. #7
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,842

    Re: Countifs unique values based on two criteria

    @ Alan - No twins? Hmmm.. did you have a dog that was quick to grab food off your plate when you weren't looking? LOL

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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