# Computing a cumulative while ignoring duplicates

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

2. ## 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. ## 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!

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