Forum Discussion
Solved
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...
- 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))
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))
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!
