Consulting

Results 1 to 6 of 6

Thread: Count matching criterias only if duplicate values are found formula ?

  1. #1

    Count matching criterias only if duplicate values are found formula ?

    Hello,

    I am wondering how I can do this :
    If criterias of column D and F are matching AND value inside a cell of column A and G matches (duplicates) then add a count.

    33 Susan Apple 48
    64 Peter Raspberry 35
    48 Susan Apple 64
    35 Susan Apple 95

    So lets say I have something like this :
    =COUNTIFS($B:$B;"Susan"; $C:$C;"Apple")... The value will be 3. However, I only want the formula to count if the value of a cell in column A and D are duplicates. Since 64 and 48 appears in both A and D, the count should be 2 instead of 3.

    What would be the appropriate formula for something like this ?

    Thank you!
    Last edited by MasterBash; 06-03-2024 at 03:18 PM.

  2. #2
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,181
    Location
    Which version of Excel are you using, and to be clear are you counting the numbers or the names?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    Quote Originally Posted by Aussiebear View Post
    Which version of Excel are you using, and to be clear are you counting the numbers or the names?
    Microsoft 365 version, fully updated.

    Oopsie, I messed up the table above. I updated it. I am trying to count the Susan + Apple as one count each, as long as number in column A shows up in column D.

    33 Susan Apple doesn't count, because 33 isn't in the 4th column.

    However,
    48 Susan Apple and 35 Susan Apple both counts (total of 2), because 48 and 35 shows up in the fourth column.

  4. #4
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,744
    Location
    Something like:

    =IFERROR(ROWS(FILTER(C1:C4,(C1:C4="Apple")*(B1:B4="Susan")*(COUNTIF(D1:D4,A1:A4)))),0)
    Last edited by georgiboy; 06-04-2024 at 01:07 AM. Reason: Disabled smilies
    Be as you wish to seem

  5. #5
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,244
    Location
    Couple of bits to look at on the attached depending on the layout you are looking for.
    Attached Files Attached Files
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2405, Build 17628.20102

  6. #6
    Quote Originally Posted by Aflatoon View Post
    Something like:

    =IFERROR(ROWS(FILTER(C1:C4,(C1:C4="Apple")*(B1:B4="Susan")*(COUNTIF(D1:D4,A1:A4)))),0)
    Thank you so much ! That works great.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •