# count unique values across multiple sheets based on date

1. ## count unique values across multiple sheets based on date

good day

i wish to count how many times a specific value occur on a specific date across different sheets. i am attaching a sample workbook.
Book1_sample.xlsx
in the workbook, the dates are extracted from the various sheets and added in cells C3:L3 (or as many dates there are)
B4:B are constants that will remain there and the values that needs to be found and counted
the values being counted are always in column F in the different sheets.

either code or a formula would work, pivot tables not ideal.

regards

2. ## Re: count unique values across multiple sheets based on date

Hi,

A couple of questions:

What do you mean by count 'unique' values in your thread title?

Just checking the first of your desired outcomes, how do you arrive at a result of 4 for 02084876 on 02-Feb-14? I see only 3: 1 in A Codes and 2 in B Codes.

Regards

3. ## Re: count unique values across multiple sheets based on date

i meant that the values are unique.
i miscounted on your second question. my apologies... (happens when one stares too long at these things)

4. ## Re: count unique values across multiple sheets based on date

Hi, try the attached

``Please Login or Register  to view this content.``
Cheers, berlan

5. ## Re: count unique values across multiple sheets based on date

1st, put all your sheet names in a small table, then give that range a name - I used "codes"

You can then use this, copied down and across...
=SUMPRODUCT(COUNTIFS(INDIRECT("'"&codes&"'!F2:F50"),\$B4,INDIRECT("'"&codes&"'!a2:a50"),C\$3))

6. ## Re: count unique values across multiple sheets based on date

Originally Posted by XOR LX
Just checking the first of your desired outcomes, how do you arrive at a result of 4 for 02084876 on 02-Feb-14? I see only 3: 1 in A Codes and 2 in B Codes.

Regards
Thats what I get too

7. ## Re: count unique values across multiple sheets based on date

thanks guys.... works brilliant

8. ## Re: count unique values across multiple sheets based on date

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

9. ## Re: count unique values across multiple sheets based on date

Using COUNTIFS this will work. I changed the "TEXT" numbers to real numbers.

Formula:
`Please Login or Register  to view this content.`

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