# Countifs through 55 columns

1. ## 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

2. ## Re: Countifs through 55 columns

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&"*")

3. ## Re: Countifs through 55 columns

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

4. ## Re: Countifs through 55 columns

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)

5. ## Re: Countifs through 55 columns

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

6. ## Re: Countifs through 55 columns

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)

7. ## Re: Countifs through 55 columns

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

8. ## Re: Countifs through 55 columns

Hehe, we are a village.

9. ## Re: Countifs through 55 columns

Hey Guys. Thanks for all the responses. I went ahead and tried out a few of the solutions but I wasn't able to get them to work. Sorry I forgot to attach the dummy data. I've gone ahead and done so. If you get a chance to look at it that would be great. Thanks!

10. ## Re: Countifs through 55 columns

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.

11. ## Re: Countifs through 55 columns

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.

12. ## Re: Countifs through 55 columns

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

13. ## Re: Countifs through 55 columns

I also noticed one difference. It will also count parts of words. For example. If it is looking for the word "Apple" it will also count for any time it finds the word "Pineapple". Is there any way to avoid this?

14. ## Re: Countifs through 55 columns

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

15. ## Re: Countifs through 55 columns

This looks good as well but when I compare my counts from this formula and the countifs it is off again. I think this could be because the title would have to have a " " before each word. It may also be because i took out special characters such as (,/,!,) etc... when I broke them out into the 55 columns. Any suggestions on how to tackle this?

16. ## Re: Countifs through 55 columns

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

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1