Countif's of uniques > number of total

**starstill** · 04-21-2010, 10:51 AM

I have attached a file.

Range #1..............Range #2
Differences.......Original Time
0:21.....................3:30 PM
0:09.....................3:09 PM
0:15.....................3:00 PM
0:06.....................2:45 PM
0:09.....................2:39 PM

...and 3000 rows of this

Range #3..............................Range #4
Countif of range 1................Uniques range #1 values

Problem:

The sum in Range #3 > number of rows/values in Range #1

That's unpossible.

Tanks for any help

**DonkeyOte** · 04-21-2010, 11:00 AM

One of these oddities where the are variances in the extreme... you could "normalise" A in C, eg:

=MROUND(ABS(A3),"0:0:0.001")*SIGN(A3)
copied down

Use Col C as the basis for either a Pivot or Filter etc to get a Count by Difference (will total 3000 I assure you !)

**DonkeyOte** · 04-21-2010, 11:05 AM

Edit: should add that a Pivot would display the 3000 correctly without need for the formula in C (ie that's redundant if you go down the PT route)

**NBVC** · 04-21-2010, 11:06 AM

If you convert the times to General and go through you will see there are duplicates in your unique range... and some that are only off by hundreds of thousandths of a decimal.. this is a problem when dealing with time and Excel's accuracy.

Try rounding column A to 4 or 5 decimals first in a new column and then use that column for unique filtering...

**starstill** · 04-21-2010, 06:07 PM

Originally Posted by NBVC

If you convert the times to General and go through you will see there are duplicates in your unique range... and some that are only off by hundreds of thousandths of a decimal.. this is a problem when dealing with time and Excel's accuracy.

Try rounding column A to 4 or 5 decimals first in a new column and then use that column for unique filtering...

How do I round an entire column?

thanks

**NBVC** · 04-22-2010, 07:36 AM

I did mention... in a new column.... ie. =ROUND(A1,4) copied down in a fresh column, where A1 is first entry to round.

**DonkeyOte** · 04-22-2010, 07:39 AM

As outlined already - if you use a Pivot you don't need to adjust your source data at all (nor apply a Unique Filter) - the Pivot will generate your desired output automatically - just set Differences as both Row Label and Data Field (set to Count)

**starstill** · 04-22-2010, 05:46 PM

Originally Posted by DonkeyOte

As outlined already - if you use a Pivot you don't need to adjust your source data at all (nor apply a Unique Filter) - the Pivot will generate your desired output automatically - just set Differences as both Row Label and Data Field (set to Count)

DonkeyOte,

Thank you for the advice. Right now I don't know how to use pivot tables (even though I bought a whole book about it). That's why I think it's a faster route to do it using NBVC's method.

Thank you for all the help, and I am sorry if I wasn't clear about my abilities before.

**DonkeyOte** · 04-22-2010, 06:10 PM

Pivots are very straightforward to use - moreover they offer great reporting flexibility for little effort.

If only to demo. for future reference - using your sample file:

Highlight A2:A3002

On the Ribbon go to the "Insert" Tab -> Pivot Table -> Click OK

Drag Differences into the Row Labels area

Drag Differences into the Values area

And that's it, you're done.

(you could have the pivot on the same sheet as the source data if preferred of course - the above is just a demo of how quick and easy it is to work with Pivots)