different values count

R

ramalina

Hi, guys!!!
need some help...

each day i record one or more record
at the end of the month I must count the working day of the mount
counting only dates that are different.

that is:

20/09/2004 abcabcabc
20/09/2004 abcabcabc
20/09/2004 abcbabcab
21/09/2004 abcbabcba
22/09/2004 abcababcb
22/09/2004 abababcba
23/09/2004 acbcbabcb

working days = 4 (the 20, 21, 22 and 23)

is there any function that count different values

i'm coming crazy to solve this problem...

thanks to you al
 
J

Jim May

Type in =SUM(1/COUNTIF(A1:A10,A1:A10)) and hold down Control+ShiftKey+Enter
afterwards it should look like this {=SUM(1/COUNTIF(A1:A10,A1:A10))}
HTH
 
A

Alex Delamain

I am sure there are some clever functions that will do it directly bu
here is a simple method using a helper column

If the dates are in column A then use a column with the formula

=if(a2=a1,"",1)

you can then sum that column to find the number of working days
 
R

Ron Rosenfeld

Hi, guys!!!
need some help...

each day i record one or more record
at the end of the month I must count the working day of the mount,
counting only dates that are different.

that is:

20/09/2004 abcabcabc
20/09/2004 abcabcabc
20/09/2004 abcbabcab
21/09/2004 abcbabcba
22/09/2004 abcababcb
22/09/2004 abababcba
23/09/2004 acbcbabcb

working days = 4 (the 20, 21, 22 and 23)

is there any function that count different values

i'm coming crazy to solve this problem...

thanks to you all
Click to expand...

1. Download Laurent Longre's morefunc.xll and use his COUNTDIFF function.

2. If you don't have too many, and if there are no blanks in the range, you can
use the ARRAY formula:

=SUM(1/COUNTIF(Dates,Dates))

3. To enter an ARRAY formula, after typing or pasting it into the formula bar,
hold down <ctrl><shift> while hitting <enter>. XL will place braces {...}
around the formula.


4. Use a Pivot Table.
Select the column of dates
Data/Pivot Table/Next/Finish
Drag the Date element into both the Rows and Data Fields. This gives
you a Pivot Table on a new sheet.

In a cell not in Column A, enter the formula:

=COUNTA(A:A)-3

The -3 is to subtract the Header and Grand Total lines that using the
defaults in the Pivot Table Wizard will cause.


--ron
 
Top