# Needing to count unique values across multiple sheets

1. ## Needing to count unique values across multiple sheets

I have a workbook setup with 6 sheets: 5 for data and one that calculates the aggregate results.

In each worksheet I have a column that has a list of numbers. Sometimes, there are multiple instances of the same number across multiple worksheets, which skews the calculations. I have a formula for the individual worksheets already that counts unique values on each individual worksheet:

=SUMPRODUCT((C2:C102<>"")/COUNTIF(C2:C102,C2:C102&""))

I would just like to get something that does this across all of my worksheets. Any advice?

2. ## Re: Needing to count unique values across multiple sheets

Hi,

If you post an attachment with an example and your desired result then I'm sure we'll be able to help.

Regards

3. ## Re: Needing to count unique values across multiple sheets

Here you go!

Time Tracking.xlsx

4. ## Re: Needing to count unique values across multiple sheets

Originally Posted by TheTubaGeek

=SUMPRODUCT((C2:C102<>"")/COUNTIF(C2:C102,C2:C102&""))
For counting unique numbers, this is more efficient:

=SUM(--(FREQUENCY(C2:C102,C2:C102)>0))

5. ## Re: Needing to count unique values across multiple sheets

Thanks. So are the values you've entered in the Aggregate sheet your expected results?

If so, what's wrong with the current set-up you have?

If not, could you please add what the actual results should be?

Regards

6. ## Re: Needing to count unique values across multiple sheets

The Aggregate just calculates the values for the entirety of the week.

The problem is that the FCR and Time per Incident values are skewed because the Tickets Handled value could (and more often than not, will), count instances where the same Incident # is listed across multiple worksheets.

7. ## Re: Needing to count unique values across multiple sheets

Sure. But I only hoped that you'd add your manually-calculated expected results in there, so at least we know what we're aiming for as a final result.

Regards

8. ## Re: Needing to count unique values across multiple sheets

There is no expected result, except for the results expected by management that I guess I could put in there. I am more using this as a personal tracking system, though.

9. ## Re: Needing to count unique values across multiple sheets

Sheet1
 A 1 1 2 2 3 3 4 4 5 5

Sheet2
 A 1 1 2 2 3 7 4 4 5 6

Count of unique numbers across the sheets:

=SUM(--(FREQUENCY(Sheet1:Sheet2!A1:A5,Sheet1:Sheet2!A1:A5)>0))

Result = 7

10. ## Re: Needing to count unique values across multiple sheets

How can there be no expected result?!

You mean with the current values in there you can't even say what the results in your Aggregate sheet should be?

If you can't, not sure I can help!

Regards

11. ## Re: Needing to count unique values across multiple sheets

The Aggregate Tickets Handled should be the total count of unique instances of each ticket number entered across all sheets. Previously, I had it as a pure aggregate where I was adding the Tickets Handled value in each sheet; however, that is not a true value of how many tickets I handled in a week, as sometimes I have to address the same Ticket Number on multiple days

12. ## Re: Needing to count unique values across multiple sheets

Tony, I tried that and get #NAME? for the result. Do I need to change the names of my worksheets?

EDIT: Figured out what I was doing wrong, and that got it working. Thanks!

13. ## Re: Needing to count unique values across multiple sheets

Originally Posted by TheTubaGeek

The Aggregate Tickets Handled should be the total count of unique instances of each ticket number entered across all sheets. Previously, I had it as a pure aggregate where I was adding the Tickets Handled value in each sheet; however, that is not a true value of how many tickets I handled in a week, as sometimes I have to address the same Ticket Number on multiple days
Ok, then Tony's method is indeed what you need.

Regards

14. ## Re: Needing to count unique values across multiple sheets

How do I mark this solved?

15. ## Re: Needing to count unique values across multiple sheets

Good deal. Thanks for the feedback!

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