Results 1 to 5 of 5

Thread: Excel Countifs forumla help with multiple criteria.

  1. #1

    Excel Countifs forumla help with multiple criteria.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello everyone,

    I've done some searching and seen similar questions asked, but I haven't been able to use these answers to figure out a solution as of yet, which is why I'm posting. My apologies if this is a redundant question, but I am in need of an answer quickly or I risk potentially losing my job!

    I am trying to get summary stats from a dataset that I have manually produced, and I would like to do this through COUNTIF or COUNTIFS. Each row represents an instance of disclosure by one company describing a relationship with another company, and in some instances, a row represents an actual document. When the info is available, I use the disclosed 'name' of the agreement or relationship, but I have occasionally included sub-types which reflect the practical nature of the relationship in an adjacent column (e.g, memorandum of understanding, representing a supply and distribution agreement).

    I would like to be able to do countifs for both of these columns for each of the major contract types in my study. This is what I have so far:

    Use concatenate to create a column combining contract type and subtype, since I want to find if a keyword is in either of these columns (it shouldn't be in both). This just seemed like an easy way to do this.

    I have keywords that I know will cover each major contract type.

    Countif= (‘rangeofcells’"*" & "keyword" & "*")


    I've gotten this bit to work just fine.

    Another column describes the quality/accuracy of any accompanying disclosed financial information. There are only a few variables that could be in this column, and I would like to be able to break down how many of each type of agreement, for a given variable in this columno. So let's say I was looking for the keyword "licens", but want to add an additional filter for a specific financial variable (for example, P, which would reflect that there is fairly full payment info available)? The only thing to add is that sometimes these variable are entered in capitals and lower case, so it would need to ignore that (so it would count p and P).

    Countif= (‘rangeofcells’"*" & "licens" & "*")


    My variables in payment column are: blank--literally blank, P, Q, V, A. I have 4 major contract types.

    There's one more hitch. How would I be able to adjust Countif= (‘rangeofcells’"*" & "license" & "*") to look for either "acqui" or "divest", since I have two keywords in this instance representing one contract type. There may actually be a few keywords I might need to use for each contract type if I want a really thorough count. Let's say hypothetically I had an agreement titled "acquisition and divestiture agreement", I would want it to still only count 1.


    Furthermore, I tried 'sheet name'! right before the range so I could use this formula in a different sheet, but I couldn't get the formatting right. How exactly does this need to be formatted in the formula?


    Please help! This is very urgent and I would very much appreciate it! If this needs clarification please don't hesitate to ask for more detail, etc.
    Last edited by ExcelNewb12; 2015-02-26 at 02:28 AM.

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,841
    Articles
    0
    Excel Version
    O365
    I would love to help you, especially if it is that critical (although any employer that holds that sort of a threat over you in what I assume is not your primary job is probably not worth working for), but I feel confused and unclear as to what your data looks like. Can you knock up a small example, change all of the names and values and the like, showing what the data looks like and preferably an example of the results. That would help us enormously.

  3. #3

    Example of data

    Hello,

    Thank you so much for replying. I have made up a mock table from some real data from the dataset. Some info is redacted but enough for you to get an idea of what I'm trying to do.

    Something I realized while doing this... that occasionally there are duplicates in the description and subtype (series 60 license | license), which in this case adds 4 to the count. I could actually avoid having to concatenate if I were able to eliminate these duplicates with the formula.

    The formula is in the first column. My dataset actually has around 32 columns. If I can get a countif working for specific keywords without getting duplicates, and then get another formula that can count those that have specific labels (most importantly P) then I will be in a good place. As you can see, I couldn't get the second formula to work (and the first has duplicates).

    One last thing... how would I correctly format the formula so that I could pull from a different sheet (I know it's something like 'sheet name'! at the start of the formula, but I think I must be missing something.

    ExcelGuruExample1.xlsx

  4. #4
    For the second formula, I think I need countifs.

    But I can't seem to get the formatting right... =COUNTIFS(F2:G55, ("*" & "licen" & "*"),[I2:I55, ("*" & "p" & "*)]

    I just tried to follow the format that shows up when you enter the formula. It shows to use brackets. I'm not sure what the correct formatting is for the wildcard search, but COUNTIF worked with just Countif= (F2:G55"*" & "licens" & "*"). I'm guessing that's where the issue is.

  5. #5
    Got it to at least recognize the function =COUNTIFS(F2:G55,("*"&"licen"&"*"),I2:I55,("*"&"P"&"*"))

    But then it gives me this error: #Value! Also just fyi, for the mock table I sent you, there is only one instance where F2:G55 would contain 'licens' and I2:I55 would contain "p".

    And unfortunately this is my main job. I currently work remotely from home but will have to present on this work fairly soon, which will determine how the project moves forward. It's extremely convenient for my family to have me here, because I am able to watch over my elderly grandmother. Just the other day she had a fall, and if I were not here to help her.... well who knows. Suffice to say it is actually very important that I get this working!

Posting Permissions

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