+ Reply to Thread
Results 1 to 3 of 3

Computing a cumulative while ignoring duplicates

  1. #1
    Registered User
    Join Date
    01-20-2010
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    8

    Computing a cumulative while ignoring duplicates

    Hi all,

    A few days ago, I posted a topic about counting the total number of charcters in a single cell, while ignoring duplicates. That post is located here ([URL="http://http://www.excelforum.com/excel-2007-help/714780-constructing-a-formula-that-counts-distinct-words-in-a-single-cell.html"]

    I was given this formula.
    =COUNT(FIND(" "&ROW(INDIRECT("1:32"))&","," "&G2&","))

    Then, CTRL+SHIFT+ENTER

    This formula works beautifully, but now I need to modify it slightly so that it can count all of the unique entries in multiple worksheets. So now it looks more like this

    =COUNT(FIND(" "&ROW(INDIRECT("1:32"))&","," "&Jan!G2&Feb!G2&Mar!G2&Apr!G2&May!G2&Jun!G2&Jul!G2&Aug!G2&Sep!G2&Oct!G2&Nov!G2&Dec!G2&","))

    Then, CTRL+SHIFT+ENTER

    It works ok, until "Jul" then after Jul, the count is off by 2. The total number of Unique should not exceed 32.

    I'm know this is confusing but any help is appreciated. I'll try to clarify questions as best as I can. Thanks!

    April


    http://www.excelforum.com/excel-2007-help/714780-constructing-a-formula-that-counts-distinct-words-in-a-single-cell.html


    MOD: thread moved to Worksheet Functions Forum from Tips & Tutorials (non question forum) - please post in most appropriate forum going forward
    Last edited by DonkeyOte; 01-22-2010 at 01:41 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Computing a cumulative while ignoring duplicates

    Instead of &Jan!G2&Feb!G2&Mar!G2 etc in your formula, try
    &Jan!G2&", "&Feb!G2&", "&Mar!G2 etc, with the same &",")) at the very end.

  3. #3
    Registered User
    Join Date
    01-20-2010
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Computing a cumulative while ignoring duplicates

    Thank you so much for responding! I tried your formula and it works a lot more consistently than the one I had. The only problem is that sometimes when the first number of a series is a single digit, it won't count unless I put a space before the digit. What do you think could be causing that? Other than that it seems to be working ok.

    Thanks for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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