If a name in a column is a match, count certain text in the row

0
votes

I have a column/list of duplicate names.

In the corresponding row/columns of duplicate names, some cells contain "Truant."

Another column contains the unique names off that same list.

I want a formula next to the Unique name (listed once) that shows how many "Truant" cells a name has beside it.

For example:

John Smith might be in Column A five times, and in the 9 cells in the same row "Truant" shows up sporadically. I have a column that contains John's unique name once. In the column next to his name, I want it to show how many Truants were in cells in the same rows as his duplicated name... I wish I could post my attachment!

1
excelmatchconditional-statementscountif

1 Answers

0
votes

I suggest in two steps:

1) count each row, say with: 

=COUNTIF(B2:J2,"Truant")

(as in K2 and copied down) then:

2) in your table of unique names sum with say: 

=SUMIF(A:A,M2,K:K)

SO34820964 example

However there are various alternatives with other functions or a PivotTable that might prove more convenient in the long-term, specially if you did not mind indicating truancy with 1 rather than Truant.