+ 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
    6

    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, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    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.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

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

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    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
    6

    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
    6

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    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