+ Reply to Thread
Results 1 to 4 of 4

attempting to create countifs function where if certain action occurs add another criteria

  1. #1
    Registered User
    Join Date
    08-30-2018
    Location
    North Bergen, NJ
    MS-Off Ver
    Office 2013
    Posts
    23

    attempting to create countifs function where if certain action occurs add another criteria

    So essentially I'm making a countifs, but the issue is this. If something specific occurs then there should be an added criteria added, if not then the countifs should only have the starting criteria. I was attempting to see I can make some kind of concatenate to attach on to the formula if the if statement is true.

    Please Login or Register  to view this content.
    This example I wrote up is based around something in my excel, to make it simple, the cell to the left C2 is being checked whether it is a or not. If it is then add a range b2:b200 with criteria that it must be "N", if not don't count. However if the cell to the left is not a then just one range and criteria.

    I hope I explained my issue well enough. It's essentially adding another range/criteria if a certain instance is met. If not don't add on to the formula. at first I was thinking countif + countif but I realized that just adds on to the count rather than restricts it.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: attempting to create countifs function where if certain action occurs add another crit

    You could do it like this:

    =COUNTIFS(a2:a200,1,b2:b200,IF(C2="a","N","*"))

    i.e. if C2 is "a" it will apply the condition B2:B200 = "N", otherwise B2:B200 could be anything (and thus not take part in the condition).

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    08-30-2018
    Location
    North Bergen, NJ
    MS-Off Ver
    Office 2013
    Posts
    23

    Re: attempting to create countifs function where if certain action occurs add another crit

    Thank You! And if it were to be a >= d2 for example I can do the same thing correct? Just change the true statement to ' ">=" d2 ' ? Also do you know how I mark this as solved? lol

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: attempting to create countifs function where if certain action occurs add another crit

    You would set that up like this:

    =COUNTIFS(a2:a200,1,b2:b200,IF(C2="a",">="&D2,"*"))

    although the wildcard character will only work with text values.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ 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. Macro to send email to designated address when an action occurs
    By emymeeky in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-28-2016, 05:19 PM
  2. [SOLVED] SUBSTITUTE as function for criteria within COUNTIFS
    By KG869 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-10-2015, 01:31 PM
  3. How to use countifs function for multiple criteria
    By Terressa in forum Excel General
    Replies: 1
    Last Post: 06-25-2015, 08:08 AM
  4. Attempting to Countifs and Averageifs between two dates
    By maskari100 in forum Excel General
    Replies: 1
    Last Post: 11-07-2014, 02:26 PM
  5. Attempting to Create an If Function
    By vbaNewbieMan in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-25-2014, 02:43 PM
  6. [SOLVED] Using The Countifs Function With Three Criteria
    By samerz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-08-2014, 01:56 PM
  7. Macro in personal worbook, attempting to reference an On Action in another XLS
    By DebbieEdwards in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-16-2013, 12:44 PM

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