COUNTIFS Question - multiple items in cell

[NeilKloster]

Hello, 

I have a Power-App that I built and I am using for QA purposes for our department.  The way that the data is feeding from the app, over to SharePoint, and then to Excel, it is showing multiple items separated by commas.  See below for an example: 

For example - Where I have 3 errors selected: 

-Cohort code is incorrect

-Graduation date incorrect in SHADEGR

-Schedule information incorrect

What I want to have is a way for COUNTIFS to read the entire column and read this as three separate errors, not just one.  My issue, is that this all feeds directly from SharePoint and when I refresh my data connection in Excel, I don't want to do any further manipulation other than that - as I want to count the specific errors on a dashboard.  

Does anyone have any ideas on this?  

Thank you!

Neil 

[#2]

@NeilKloster 

I guess you are on Excel with dynamic arrays. You may calculate number of commas in entire column as

=SUM(LEN(I:I)-LEN(SUBSTITUTE(I:I,",",""))+(LEN(I:I)>0))

but for better performance preferably  to take the range or table column.

We assume there are no commas within error text, they are only separators.

[#3]

Thank you so much for your reply. I'm still a bit new to everything. I'm looking to for something similar to this, but I want it to count the specific errors in a cell. So, my example had three errors in one cell. I need a formula that will count the specific times a specific error is received per QA submission.

So I need it to tell me how many times "Graduation date incorrect" etc. occurred in column "I" - as I want to use this column for my COUNTIFS formula, even though there will be times that multiple errors are selected (and thus when I get 2 or 3 errors in one cell separated by commas).

Does that make more sense?

Thank you for your help and assistance.

Neil

[#4 · Accepted solution]

@NeilKloster 

Neil, let assume the text of the error you'd to count is in the cell A1. When

=COUNTIF(I:I,"*" & A1 & "*")

[#5]

@Sergei Baklan 

Wow!  I think that did it.  Let me explore this a little bit and tweak it to what I need, but I think that may have solved my issue.  Thank you so much!!

Neil 

[#6]

@NeilKloster 

Neil, that doesn't work if the same error is repeated in one cell more than once, if that's a case. Otherwise shall work.

[#7]

@Sergei Baklan 

Thank you so much for your responses!  I feel like I'm very close here.  I have another question, I edited the formula as such: 

=COUNTIF(I:I,"*" & "Missing minimum of 45 clinical weeks" & "*") and that returned a result of 3, which is good, because that error occurs 3 x in that column. 

How do I use this formula to combine it with another factor?  So for example, I want to count the number of a specific type of error (column I) as well as how many times that error occurred in say the Spring 2020 semester (column F).  How would I write that?  

I will have a few instances where I need to review the number of a specific error, the semester that it happened and the person that did it, among probably a few others here and there, but would something like that be written?  

[#8]

@NeilKloster 

That is job for COUNTIFS()

for above sample the formula is

=COUNTIFS(I:I,"*"&$K$2&"*",F:F,$L$2)

[#9]

@Sergei Baklan 

This helped!!  I was able to make it work.  Thank you so much!!