Forum Discussion
Count comma separated values in column
Hello,
I am looking for the formula to count items in a column in excel, however, some cells will have two or more items in them, separated by commas. I need the COUNTIFS formula to count based on my criteria the specific program from one column, the session, and then the number of errors in column "I" based on my specified criteria.
I need a formula that will count the number of times each program in "CU Program" occurred, in the "May 2020" session and then the number of errors. For example, I am looking for the number of errors that occurred for RN-BSN in the May 2020 session. I can see that it is two, but this list will continue to grow and gather data from Sharepoint with each refresh, so there will be cells with multiple CSV's in it.
Any thoughts?
- Say G1 and H1 contain your criteria for CU Program and Session, respectively:
If you want to count "Incorrect Letter Sent, SUAMAIL Code Missing" as two errors even though it is only in one cell , then:
=SUMPRODUCT(--(Table1[CU Program]=G1),--(Table1[Session]=H1),--(LEN(Table1[CU Service Errors])>0),LEN(Table1[CU Service Errors])-LEN(SUBSTITUTE(Table1[CU Service Errors],",",""))+1)
Otherwise, if that entry only counts as one error, then:
=SUMPRODUCT(--(Table1[CU Program]=G1),--(Table1[Session]=H1),--(LEN(Table1[CU Service Errors])>0))
3 Replies
- Say G1 and H1 contain your criteria for CU Program and Session, respectively:
If you want to count "Incorrect Letter Sent, SUAMAIL Code Missing" as two errors even though it is only in one cell , then:
=SUMPRODUCT(--(Table1[CU Program]=G1),--(Table1[Session]=H1),--(LEN(Table1[CU Service Errors])>0),LEN(Table1[CU Service Errors])-LEN(SUBSTITUTE(Table1[CU Service Errors],",",""))+1)
Otherwise, if that entry only counts as one error, then:
=SUMPRODUCT(--(Table1[CU Program]=G1),--(Table1[Session]=H1),--(LEN(Table1[CU Service Errors])>0))- You are welcome!
