+ Reply to Thread
Results 1 to 8 of 8

How to count the number of criteria in a column when there is multiple criteria in a cell

  1. #1
    Registered User
    Join Date
    04-07-2021
    Location
    St. Paul
    MS-Off Ver
    MS Office 365 ProPlus
    Posts
    3

    Question How to count the number of criteria in a column when there is multiple criteria in a cell

    Hello! Thank you for taking the time to look at my issue. I'm sure this will be take you .2 seconds vs the 30 minutes of me getting no where haha.

    What I'm trying to do:
    Populate the number of levels based on "Level" column in screenshot 2.
    Screenshot 1:
    1.PNG

    Screenshot 2:
    2.PNG

    For example, the L01 level should have 27 instances in the "Level" column. I've attached the spreadsheet to this thread.

    Thank you and please let me know if you have any questions.
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,671

    Re: How to count the number of criteria in a column when there is multiple criteria in a c

    Explain the logic that that you used to get to 27. I am not understanding how to get that number. What are some of the other expected results and what is the correlation between the two worksheets. How did you arrive at the titles vs. the lines of data in the second sheet. We need more information. Mind reading is not my specialty.

  3. #3
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: How to count the number of criteria in a column when there is multiple criteria in a c

    Two ways:

    C2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    or
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The latter is more efficient, but the former would be necessary if any level appearing in 'Help me here'!A2 and below could appear multiple times in any cell in Level!A:A.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: How to count the number of criteria in a column when there is multiple criteria in a c

    Try this:

    =COUNTIF(Level!$A$2:$A$63,"*"&[@Level]&"*")
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,671

    Re: How to count the number of criteria in a column when there is multiple criteria in a c

    Just playing around with Power Query. Is this the results you are looking for:

    Excel 2016 (Windows) 32 bit
    A
    B
    1
    Level.1 Count
    2
    L01
    27
    3
    L3
    12
    4
    L9
    3
    5
    L1
    11
    6
    L4
    5
    7
    1
    8
    L7
    1
    9
    L6
    1
    10
    L5
    1
    Sheet: Table2

  6. #6
    Registered User
    Join Date
    04-07-2021
    Location
    St. Paul
    MS-Off Ver
    MS Office 365 ProPlus
    Posts
    3

    Re: How to count the number of criteria in a column when there is multiple criteria in a c

    Hi Alansidman, I used a bad formula to find out the number of L01 in the Level column. I'm looking for 38 L2s for example (I counted by hand this time). I'm sorry it wasn't clear.
    Thank you

  7. #7
    Registered User
    Join Date
    04-07-2021
    Location
    St. Paul
    MS-Off Ver
    MS Office 365 ProPlus
    Posts
    3

    Re: How to count the number of criteria in a column when there is multiple criteria in a c

    Thank you hrlngrv and AliGW for all your help, these formulas work brilliantly!

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: How to count the number of criteria in a column when there is multiple criteria in a c

    Glad to help.

+ 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. [SOLVED] Countifs multiple criteria- criteria if cell is greater than another i adjacent column
    By Sircool1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-26-2018, 11:23 AM
  2. Replies: 4
    Last Post: 11-01-2016, 03:28 PM
  3. Count number of Rows with multiple criteria (multiple valued cells)
    By garog in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-09-2014, 01:33 PM
  4. Replies: 1
    Last Post: 02-21-2014, 09:09 PM
  5. Count number occurences based on criteria Column
    By SamCrome in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2013, 04:32 PM
  6. Replies: 3
    Last Post: 04-05-2011, 07:09 PM
  7. Count number of rows with multiple criteria
    By spinx in forum Excel General
    Replies: 5
    Last Post: 09-17-2010, 09:17 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