In Excel, how to Count the number of times words appear in adjacent columns

Barbara Hippe 60

I have over a thousand rows, 3 columns, of many keywords associated with types of books.

I want to Excel to first identify each discrete keyword and count how many times it appears in those 1000+ records

business reference economics

fiction popular mystery

children set fiction

history american popular

0 comments

Answer accepted by question author

Barry Schwarz 6,106

My testing was able to produce a SUMIF problem that appears to match yours. I don't know what causes it or how consistent it is. It appears that SUMIF did not recognize a match when the value being tested against the criteria is not in the first column of the range. The value "Best Seller" in F:H that should match U1284 is in G or H but not in F. This results in a sum of 0 and your accounting format causes that to display as a dash.

The following workaround appears to produce the desired result:

=SUM(IF((--($F$6:$F$1537=U1280))+(--($G$6:$G$1537=U1280))+(--($H$6:$H$1537=U1280)),$E$6:$E$1537,0))

This goes in W1280 and gets copied down till the last key word in column U.

If you put it in column X instead, you may discover some of the values in W are incorrect but don't catch the eye the way the dash did.

Barbara Hippe 60 Reputation points

2026-08-12T14:10:25.2466667+00:00

Thank you, Barry. I am doing a manual spot check. Looks great so far and if holds up will Accept Answer/Solve Problem!

Answer accepted by question author

Ashish Mathur 102.4K Volunteer Moderator

Hi,

In cell F2, enter this formula

=LET(a,TOCOL(A2:C7,1),GROUPBY(a,a,COUNTA,,0))

Hope this helps.

User's image

Barbara Hippe 60 Reputation points

2026-08-10T13:18:58.79+00:00

Thank you! That works.

Then I copy, paste and sort the results into new columns to get a total revenue for each genre, which is my ultimate goal. The SUMIF works, mostly. But I can't figure out why no $ value for some genres. No error pops up, just the dash. Any ideas appreciated!

These are columns U, V, W.

F - H are the keywords, U is the genre cell address (Design is in U1280), and E is the Revenue column where I have a value for each of the over 1500 sales.

In the W column: =SUMIF($F$6:$H$1537,U1280,$E$6:$E$1537)
Ashish Mathur 102.4K Reputation points Volunteer Moderator

2026-08-10T22:42:09.0366667+00:00

Hi,

Please share sample data in a format that can be pasted in an MS Excel file.
Barbara Hippe 60 Reputation points

2026-08-11T12:44:57.9733333+00:00

This is the best I can do unless you can suggest a specific file type.
Barbara Hippe 60 Reputation points

2026-08-11T12:47:55.6133333+00:00

Sales Records Sample.txt
Ashish Mathur 102.4K Reputation points Volunteer Moderator

2026-08-11T22:50:27.8433333+00:00

Share the download link of the MS Excel file.
Barbara Hippe 60 Reputation points

2026-08-12T12:42:06.4533333+00:00

This site does not allow Excel file attachments:

Answer accepted by question author

riny 21,520 Volunteer Moderator

@Barbara Hippe

Assuming you have a modern Excel version at your disposal!

User's image

The picture shows the formula in F1. then in G1 I use:

=COUNTIF(A1:C4,F1#)

Is that what you had in mind?

Barbara Hippe 60 Reputation points

2026-08-09T14:00:31.6733333+00:00

Thank you! The first part, your column F1, works. The second part returns all zeros in column G (column P on my end)
Barbara Hippe 60 Reputation points

2026-08-09T14:16:36.6633333+00:00

Yeah! My mistake. It worked! Thanks again!

Barbara

1 additional answer

Marcin Policht 107.5K Reputation points MVP Volunteer Moderator

2026-08-09T13:34:24.8866667+00:00
If your 3 columns contain multiple keywords separated by spaces, you can have Excel identify every distinct keyword across all 1,000+ rows and count how many times each keyword appears.

Assuming your data is in A2:C1001, enter this formula in an empty cell:
```
=LET(x,TEXTSPLIT(TEXTJOIN(" ",TRUE,A2:C1001)," "),u,UNIQUE(x),HSTACK(u,COUNTIF(x,u)))
```
Excel will return two columns: the unique keyword and its total count across all three columns and all rows. For example, if “fiction” appears 150 times anywhere in the three columns, the result will show “fiction” and “150”.

This should work when each keyword is separated by a space, such as “business reference economics” or “fiction popular mystery”. If your actual cells contain keywords separated by commas, semicolons, or another delimiter, the formula needs to be adjusted to that delimiter.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin
Was this answer helpful?
Barbara Hippe 60 Reputation points

2026-08-09T13:47:52.65+00:00

Hi, Marcin, Thank you. My keywords are actually in individual cells. For example F6 contains Art.

Barbara Hippe 60 Reputation points

2026-08-09T14:12:45.7733333+00:00

Here is an image:
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

In Excel, how to Count the number of times words appear in adjacent columns

1 additional answer

Your answer