+ Reply to Thread
Results 1 to 4 of 4

Countifs with selective if statement

Hybrid View

  1. #1
    Registered User
    Join Date
    03-09-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    2

    Countifs with selective if statement

    I have worked a way around the problem but it is not very elegant as I need to use it many times.
    I have a countifs statement that works well but I have an additional selection box which holds a list of years. All I would like is if the box says "All" then to not include it in the countifs statement, however if it says a year such as "2012" to use that in the countifs statement. Tis is what I have currently got:

    =IF(J3="All",
    COUNTIFS(Results[First Set Winner],"W",Results[Best of],INDIRECT("J2")),
    COUNTIFS(Results[First Set Winner],"W",Results[Best of],INDIRECT("J2"),Results[Year],INDIRECT("J3")))
    Thanks

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Countifs with selective if statement

    hi Mulveriser, welcome to the forum. i'm not really sure until i see the file, but i think this should work:
    =COUNTIFS(Results[First Set Winner],"W",Results[Best of],INDIRECT("J2"),Results[Year],IF(J3="All","*",INDIRECT("J3")))

    and it seems like you don't need INDIRECT
    =COUNTIFS(Results[First Set Winner],"W",Results[Best of],J2,Results[Year],IF(J3="All","*",J3))

    let us see the file, if needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    03-09-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Countifs with selective if statement

    Thanks Benishiryo,
    You are correct I didn't need the indirect function, not quite sure why I put it in there to be honest :s

    What you gave me was ideal as I didn't know where to put the "if" part of the statement. It just needed 1 slight tweek:
    =COUNTIFS(Results[First Set Winner],"W",Results[Best of],J2,Results[Year],IF(J3="All","<>",J3))
    It had to be "<>" instead of your suggested "*"

    Thanks for helping!

  4. #4
    Registered User
    Join Date
    03-02-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Countifs with selective if statement

    I am removing my earlier suggestion to continue to use the same as being elegant.
    Last edited by tgsekhar; 03-09-2013 at 10:01 PM. Reason: Some Improvements have been suggested by other members already.

+ 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