Excel COUNTIF formula to search multiple columns for a string


Sometimes in Excel you may want to search for a string in more than one column. The COUNTIF formula is perfect for this task. It was previously referenced in the tip of the night for October 9, 2015, when I described a formula to count the number of times a string appeared in a range of cells. Here's a demonstration of the simpler COUNTIF formula. We have a spreadsheet where we want to return an entry of the player's name in column B, when that player either had 20 or more assists or errors in a season0, which are listed in columns J and K. So the formula appears this way in row 29 of the spreadsheet.

=IF(COUNTIF(J29:K29,">=20"),C29,"")

. . . and it returns a value from column C because Henry Aaron had 38 assists in 1971,

Rather than specifying a value in the formula you can also input a cell reference, [where ">=20" is in this example] so you're searching against a list of values in another column, in the range of specified cells.