+ Reply to Thread
Results 1 to 9 of 9

COUNTIF Counting for Only One Occurrence in Two Columns

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-25-2013
    Location
    Virginia, USA
    MS-Off Ver
    Microsoft 365 Version 2409 64-bit
    Posts
    246

    COUNTIF Counting for Only One Occurrence in Two Columns

    I have three columns (A,B,C) and four rows (1,2,3,4). In column “A” the value “100” is entered in all four rows. In column “B” the values are “ACC”, “SEC”, “AAC”, & “AAC”. In column “C” the values are “AAC”, “AAC”, “SEC”, & “AAC”.

    I'm looking for a formula that counts the number of times the following conditions occur: the value "100" in column A and "AAC" in either column "B" or column "C". So, in the example the there are four occurrences where there is a "100" in column "A" and an "AAC" in column "B" or "C". What formula will give me the value "4"?
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: COUNTIF Counting for Only One Occurrence in Two Columns

    In your example, column A is irrelevant, as all values in A are 100.

    So, in therory, this will do it:

    =SUMPRODUCT(--(B1:C4="AAC"))-COUNTIFS(B1:B4,"AAC",C1:C4,"AAC")

    However, if column A CAN have values other than 100, then you need:

    =SUMPRODUCT((A1:A4=100)*(B1:C4="AAC"))-COUNTIFS(B1:B4,"AAC",C1:C4,"AAC")
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,706

    Re: COUNTIF Counting for Only One Occurrence in Two Columns

    A few options, two SUMPRODUCT approaches for ex.

    Formula: copy to clipboard
    =SUMPRODUCT((A1:A4=100)*ISNUMBER(SEARCH("AAC",B1:B4&"^"&C1:C4)))


    or

    Formula: copy to clipboard
    =SUMPRODUCT((A1:A4=100)*SIGN((B1:B4="AAC")+(C1:C4="AAC")))


    If you've just the two columns in the "OR" test then I would go with option 2 -- if you've more then option 1 will be more succinct.

  4. #4
    Forum Contributor
    Join Date
    08-25-2013
    Location
    Virginia, USA
    MS-Off Ver
    Microsoft 365 Version 2409 64-bit
    Posts
    246

    Re: COUNTIF Counting for Only One Occurrence in Two Columns

    I made a mistake. I didn't give all the possible variables in my first example. In the attached example there are nine instances in which there is an "AAC" occurring in columns "B" or "C". What I'm looking for is a formula that will produce the values in cells "E2" (4), "F2" (4), & "G2"(1). "E2"s value is the number of times the value "100" appears in column "A" and "AAC" appears in either "B" OR "C". "F2"s value is the number of times the value "-110" appears in column "A" and "AAC" appears in either "B" or "C". "G2"s value is the number of times the value "0" appears in column "A" and "AAC" appears in either "B" or "C".
    Attached Files Attached Files

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: COUNTIF Counting for Only One Occurrence in Two Columns

    Change E1-G1 to real numbers. Then use this, copied across:

    =SUMPRODUCT(($A$1:$A$104=E1)*(ISNUMBER(SEARCH("AAC",$B$1:$B$104&$C$1:$C$104))))
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    08-25-2013
    Location
    Virginia, USA
    MS-Off Ver
    Microsoft 365 Version 2409 64-bit
    Posts
    246

    Re: COUNTIF Counting for Only One Occurrence in Two Columns

    Oh, I see the confusion. The values in cells "E2", "F2" & "G2" should be where the formula goes. So pretend that the only information given is in columns "A", "B", & "C". I was just showing what values the formula SHOULD show given the data in columns "A", "B", & "C".

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: COUNTIF Counting for Only One Occurrence in Two Columns

    Change number in red to whatever you want...

    =SUMPRODUCT(($A$1:$A$104=100)*(ISNUMBER(SEARCH("AAC",$B$1:$B$104&$C$1:$C$104))))

    I was just using Excel to pick up the number to be searched from row 1. Why not?? But you can hard-code it into the formula as shown above, if you prefer.

  8. #8
    Forum Contributor
    Join Date
    08-25-2013
    Location
    Virginia, USA
    MS-Off Ver
    Microsoft 365 Version 2409 64-bit
    Posts
    246

    Re: COUNTIF Counting for Only One Occurrence in Two Columns

    My mistake. Thank you! I will mark as solved.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: COUNTIF Counting for Only One Occurrence in Two Columns

    LoL!! You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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. Counting past occurrence given a classification
    By makae90 in forum Excel General
    Replies: 7
    Last Post: 09-29-2017, 01:49 PM
  2. IF Statement / Occurrence counting
    By DHarper in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-29-2013, 10:49 AM
  3. Replies: 2
    Last Post: 04-18-2013, 02:03 PM
  4. Excel 2007 : Counting Occurrence of value across workbooks
    By jonwickert in forum Excel General
    Replies: 0
    Last Post: 05-03-2012, 10:47 AM
  5. Replies: 2
    Last Post: 02-23-2010, 03:01 AM
  6. Replies: 4
    Last Post: 09-24-2009, 09:57 AM
  7. Replies: 3
    Last Post: 08-20-2009, 06:08 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