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

1. ## 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.

2. ## 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. ## Re: How to count the number of criteria in a column when there is multiple criteria in a c

Two ways:

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

or
Formula:
`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. ## 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]&"*")

5. ## 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. ## 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. ## 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. ## Re: How to count the number of criteria in a column when there is multiple criteria in a c

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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