Countifs through 55 columns

I am working on a project at work where we pull apart the titles of products into individual keywords. From there we put a count on how many times that keyword was put under a specific category. The problem that I have is that there are titles that get up to 55 words. Each title is spread across a row and only one word per cell on that row. This requires me to create 55 different countifs formulas and add them together.

My question is, is there a way to do this that requires less processing time? I am doing this for thousands of cells and when I finish this spreadsheet it will end up taking about 30-40 minutes to process. I've tried a macro as well but it is too much for excel to handle.

I've attached some dummy data that include my actual formula. I only put the formula through one line so it wouldn't take forever to calculate.

Just so you can see what I am working with here is the formula

=COUNTIFS('1 Word Title Dump'!$E:$E,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$F:$F,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$G:$G,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$H:$H,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$I:$I,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$J:$J,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$K:$K,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$L:$L,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$M:$M,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$N:$N,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$O:$O,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$P:$P,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$Q:$Q,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$R:$R,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$S:$S,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$T:$T,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$U:$U,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$V:$V,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$W:$W,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$X:$X,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$Y:$Y,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$Z:$Z,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AA:$AA,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AB:$AB,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AC:$AC,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AD:$AD,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AE:$AE,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AF:$AF,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AG:$AG,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AH:$AH,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AI:$AI,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AJ:$AJ,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AK:$AK,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AL:$AL,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AM:$AM,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AN:$AN,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AO:$AO,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AP:$AP,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AQ:$AQ,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AR:$AR,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AS:$AS,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AT:$AT,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AU:$AU,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AV:$AV,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AW:$AW,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AX:$AX,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AY:$AY,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$AZ:$AZ,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$BA:$BA,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$BB:$BB,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$BC:$BC,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$BD:$BD,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$BE:$BE,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)+COUNTIFS('1 Word Title Dump'!$BF:$BF,'Unique 1 Word List'!$A2,'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)

Any suggestion would be useful.



Sorry. I forgot to add the dummy data. Here it is
DummyData.xlsx

I don't see the dummy data.

But the idea that comes immediately to mind is to add a helper column to the data to concatenate all the words together into a single column. Like so:

=TRIM(B2&" "&C2&" "&D2&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" "&J2&" "&K2&" "&L2&" "&M2&" "&N2&" "&O2&" "&P2&" "&Q2&" "&R2&" "&S2&" "&T2&" "&U2&" "&V2&" "&W2&" "&X2&" "&Y2&" "&Z2&" "&AA2)

....obviously you would need to continue this out through column BF. Once that new cell is created, copy that down the data set.

Your COUNTIF() can now simplify down significantly. Let's assume this new column is BZ, then:

=COUNTIF('1 Word Title Dump'!$BZ:$BZ, "*"&'Unique 1 Word List'!$A2&"*")

I have no idea if this will work or not (cant find the dummy data?), but take a look...

=sumproduct(--('1 Word Title Dump'!$E:$BF='Unique 1 Word List'!$A2),--('1 Word Title Dump'!$B:$B='Unique 1 Word List'!B$1))

Note: change the column ranges to just cover your data

That's a nice idea, Jerry but you might get a false match if any of the words are substrings of other words, e.g. if you are searching for red you'd get a match with bored

You can tweak it slightly by putting a space at the start and end of the string in BZ, e.g. use this formula with all cell values filled in

=TRIM(" "&E2&" "&F2&" "&G2&" "&H2&" "&..........&" "&BD2&" "&BE2&" "&BF2&" ")

Then COUNTIFS can be

=COUNTIFS('1 Word Title Dump'!$BZ:$BZ," *"&'Unique 1 Word List'!$A2&"* ",'1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)

Originally Posted by FDibbins

I have no idea if this will work or not (cant find the dummy data?), but take a look...

=sumproduct(--('1 Word Title Dump'!$E:$BF='Unique 1 Word List'!$A2),--('1 Word Title Dump'!$B:$B='Unique 1 Word List'!B$1))

Note: change the column ranges to just cover your data

You wouldn't be able to use that syntax, Ford. Only multiplication will work when the numbers of columns differs, e.g.

=sumproduct(('1 Word Title Dump'!$E:$BF='Unique 1 Word List'!$A2)*('1 Word Title Dump'!$B:$B='Unique 1 Word List'!B$1))

......but even cutting down to the specific ranges would be slower than COUNTIFS

DDL, wouldn't it be:
=COUNTIFS('1 Word Title Dump'!$BZ:$BZ, "* " & 'Unique 1 Word List'!$A2 & " *", '1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)

(notice the spaces have been moved inward)

Originally Posted by JBeaucaire

DDL, wouldn't it be:
=COUNTIFS('1 Word Title Dump'!$BZ:$BZ, "* " & 'Unique 1 Word List'!$A2 & " *", '1 Word Title Dump'!$B:$B,'Unique 1 Word List'!B$1)

(notice the spaces have been moved inward)

Hello Jerry,

Yes, of course, my mistake, thanks for the correction. The idea, as you know, is that we search for " red " surrounded by spaces so that it can't get a false match. But of course that means the spaces need to be on the inside

Hehe, we are a village.

Your dummy data reveals your original question is goose chase. You can ignore the 55 columns and simply match from column A.

Put this formula in B2 and copy across, then down:

=SUMPRODUCT(ISNUMBER(SEARCH($A2, '1 Word Title Dump'!$A$3:$A$1002))*('1 Word Title Dump'!$B$3:$B$1002=B$1))

This is an array formula, it may take a bit of time for all the formulas to calculate.

Originally Posted by JBeaucaire

Your dummy data reveals your original question is goose chase. You can ignore the 55 columns and simply match from column A.

Put this formula in B2 and copy across, then down:

=SUMPRODUCT(ISNUMBER(SEARCH($A2, '1 Word Title Dump'!$A$3:$A$1002))*('1 Word Title Dump'!$B$3:$B$1002=B$1))

This is an array formula, it may take a bit of time for all the formulas to calculate.

Thank you so much. That worked so much faster than my way.

There is only one downside to it now but it will work for my needs. If there are two of the same word in one title I think it will only count it once.

No, it there are two matching words, it will count in the correct Category column on both rows where those multiple words match.

You're back to the "false positives" I mentioned earlier. If you want to avoid those but also count words twice if they appear twice (e,g. count "NAJA" twice from "NAJA NAJA VENOM" in A110) then I think you are back to examining the 55 columns.

try this version in B2 copied across and down

=SUMPRODUCT(ISNUMBER(SEARCH(" "&$A2&" "," "& '1 Word Title Dump'!$E$3:$BG$1002&" "))*('1 Word Title Dump'!$B$3:$B$1002=B$1))

It'll be a bit slower, though

can you give me an example of a discrepancy between that formula and your COUNTIFS version?