dannac Posts: 267, Reputation: 9 Full Member #1 Feb 21, 2014, 08:55 AM
Count unique dates on different sheets
My workbook has 26 sheets as pictured below.

On sheet 27 I would like to set up some type of formula that will count the unique dates from all 26 sheets. Dates will always be from A15:A30.

Below example would give a count of 6.

 dannac Posts: 267, Reputation: 9 Full Member #2 Feb 21, 2014, 09:01 AM
Forgot to mention, sheets are as 01, 02, 03,. 10, 11, 12, etc .
 ScottGem Posts: 64,966, Reputation: 6056 Computer Expert and Renaissance Man #3 Feb 21, 2014, 09:14 AM

You can add a cell in each sheet that counts the unique dates, then have a summary cell to add them from all the sheets.
 talaniman Posts: 54,227, Reputation: 10853 Expert #4 Feb 21, 2014, 09:22 AM
I point out that B-E inclusively counts as one cell.
 dannac Posts: 267, Reputation: 9 Full Member #5 Feb 21, 2014, 12:27 PM

This formula works as long as its on the same sheet.

=SUMPRODUCT(--(FREQUENCY(\$A\$15:\$A\$30,\$A\$15:\$A\$30)>0))

I tried having it look at a different sheet but cannot figure the correct syntax.
 dannac Posts: 267, Reputation: 9 Full Member #6 Feb 25, 2014, 05:07 AM
With my sheets as 01, 02, 03, etc... I would like the formula below on different sheet.

I get the error message when I try to reference a different sheet.

Anyone see something wrong with the syntax ?

=sumproduct('01'!--(Frequency(a15:a30,a15:a30)>0))
 JBeaucaire Posts: 5,426, Reputation: 997 Software Expert #7 Mar 8, 2014, 11:03 AM
Sheet references must be attached to each range of cell references:

=SUMPRODUCT(--(Frequency('01'!a15:a30,'01'!a15:a30)>0))
 dannac Posts: 267, Reputation: 9 Full Member #8 Mar 10, 2014, 04:05 AM
Thanks JB

 Question Tools Search this Question Search this Question: Advanced Search

## Check out some similar questions!

Hi guys! I have a problem. I want all of your help in solving the problem. For the purpose, I have created a sample sheet and enclosed: The sample sheet contains four worksheets in it. The first worksheet titled “schools performance” is the master sheet and all the next three work sheets (sub...

I pay a monthly assessment fee to my homeowners association. I always pay in person and obtain a dated receipt for my payment, but on two of the monthly statements, my payments were posted days after the dates on my receipts of payment. This resulted in being charged for a late fee on one and on...

1st count has been set by default On the second count my attorney told me that we have a trail date on Friday (nov 4th which I understood it was in front of a jury) somehow today he tells me we do not have the right to a jury trial What is the reason for this?

How to count unique values in specific names [ 1 Answers ]

Hi guys, Anyone can help me what is the formula in excel MS office 2007 Of counting the unique numbers in one column A against column B Example: Column A (Cheque Numbers) Column B 1234 Charice 1235 ...

I don't understand, can doctors be wrong about conception dates and due dates?