+ Reply to Thread
Results 1 to 16 of 16

Countifs through 55 columns

  1. #1
    Forum Contributor
    Join Date
    05-24-2014
    MS-Off Ver
    Microsoft Office 2013
    Posts
    113

    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
    Last edited by manofcheese; 04-20-2015 at 02:31 PM. Reason: Forgot Dummy Data

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    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&"*")
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,310

    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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)
    Audere est facere

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Countifs through 55 columns

    Quote Originally Posted by FDibbins View Post
    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. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    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. #7
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Countifs through 55 columns

    Quote Originally Posted by JBeaucaire View Post
    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. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Countifs through 55 columns

    Hehe, we are a village.

  9. #9
    Forum Contributor
    Join Date
    05-24-2014
    MS-Off Ver
    Microsoft Office 2013
    Posts
    113

    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. #10
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    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. #11
    Forum Contributor
    Join Date
    05-24-2014
    MS-Off Ver
    Microsoft Office 2013
    Posts
    113

    Re: Countifs through 55 columns

    Quote Originally Posted by JBeaucaire View Post
    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.
    Last edited by manofcheese; 04-20-2015 at 02:35 PM.

  12. #12
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    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. #13
    Forum Contributor
    Join Date
    05-24-2014
    MS-Off Ver
    Microsoft Office 2013
    Posts
    113

    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. #14
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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
    Last edited by daddylonglegs; 04-20-2015 at 03:13 PM.

  15. #15
    Forum Contributor
    Join Date
    05-24-2014
    MS-Off Ver
    Microsoft Office 2013
    Posts
    113

    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. #16
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Countifs through 55 columns

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. SUM COUNTIFS across columns within a table and across tables...
    By scottmail in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 03-22-2015, 10:47 PM
  2. countifs - four columns
    By dulitul in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-15-2013, 01:25 PM
  3. Replies: 0
    Last Post: 12-16-2011, 09:01 AM
  4. Using COUNTIFS with multiple columns
    By djsquare in forum Excel General
    Replies: 1
    Last Post: 06-27-2011, 01:58 PM
  5. [SOLVED] Countifs and multiple columns...
    By ajayb in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-09-2006, 10:35 AM

Tags for this Thread

Bookmarks

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