+ Reply to Thread
Results 1 to 8 of 8

How to use CountIF() with a condition?

  1. #1
    Registered User
    Join Date
    07-09-2013
    Location
    Las Vegas
    MS-Off Ver
    Excel 2010
    Posts
    3

    How to use CountIF() with a condition?

    I have a range of multi column data and use CountIf() to count the number of occurrences of text match a particular critera.
    For example counting how many times "abc" occurs in the a range of 21 different columns. I have this part working fine.

    BUT, we now also need to know if there is an "x" entered in the 3rd column to the right of the cell in which the match is found and; Count only if the value is found.

    Below is the part that is working.
    =COUNTIF($A$4:$AB$54,TEXT(AD4,""))

    My issue is figuring out how to put in a condition so that the cell is not counted unless there is also an "x" in a cell 3 columns to the right, that has me stumped.

    Thanks in advance for your help!
    -DL

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How to use CountIF() with a condition?

    Try countifS

    =COUNTIFS($A$4:$AB$54,TEXT(AD4,""),$D$4:$AE$54,"X")

    Notice the offset by 3 columns on the 2nd range ($D$4:$AE$54).
    You may need to adjust that depending on exactly what you mean by "3 columns to the right"

    Take column A, is 3 columns to the right C or D ?
    If you say D is 3 columns to the right of A, then the formula as posted is correct.
    If you say C, then adjust the 2nd range by 1 column to $C$4:$AD$54

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to use CountIF() with a condition?

    =SUMPRODUCT(--($A$4:$AB$54=1),--(OFFSET($A$4:$AB$54,0,3)="x")) should work
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    07-09-2013
    Location
    Las Vegas
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: How to use CountIF() with a condition?

    Hi Jonmo1 and martindwilson

    Thanks for your replies. I don't think either will work. the data looks like this:

    Item Lbl1 Lbl2 Chk'd Item Lbl1 Lbl2 Chk'd Item Lbl1 Lbl2 Chk'd
    abc widget lidget x abc widget zidget dfg widget zidget
    cdf widget lidget cdf widget zidget x cdf widget zidget x
    abc widget lidget x abc widget zidget x abc widget zidget x

    So moving the range 3 cells would only work for the left most group. I think we need tosomehow know the cell ID of the cell that CountIf is evaluating and then offset.

    -DL

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How to use CountIF() with a condition?

    Works for me, see formula in AD1
    EFdlowrey.xlsx

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to use CountIF() with a condition?

    no that offsets every cell in the range and looks at it
    =SUMPRODUCT(--($A$4:$AB$54="abc"),--(OFFSET($A$4:$AB$54,0,3)="x"))
    with the above pasted into a4 to l7 gives a count of 4 for abc

  7. #7
    Registered User
    Join Date
    07-09-2013
    Location
    Las Vegas
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: How to use CountIF() with a condition?

    Wow,

    You guys are genius. That works great.

    Thank you!

    -DL

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How to use CountIF() with a condition?

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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