I have a long 16000 lines sheeet that contains the checking per day per
Section.
ie
Jan 1, Sales, Mr X
Jan 1, Sales, Mr Y
Jan 1, Marketing, Mr Z
Jan 2, Sales, Mr X
Jan 2, Marketing, Mr Z

I want to add the functiion like COunt, or dcount
Jan 1, Sales 2, people
Jan1, Marketing, 1 person
Jan 2, Sales, 1 Person
etc...

=COUNT(CELL RANGE)

or

= COUNTA(CELL RANGE)

"stratis" wrote:

I have a long 16000 lines sheeet that contains the checking per day per
Section.
ie
Jan 1, Sales, Mr X
Jan 1, Sales, Mr Y
Jan 1, Marketing, Mr Z
Jan 2, Sales, Mr X
Jan 2, Marketing, Mr Z

I want to add the functiion like COunt, or dcount
Jan 1, Sales 2, people
Jan1, Marketing, 1 person
Jan 2, Sales, 1 Person
etc...

A Pivot Table is probably the easiest approach....
Just make sure you have column headings
(I'll assume: Date,Category,Name)

From the Excel Main Menu....
<Data<Pivot Table
Use: Excel
Select your data
Click the [Layout] button

ROW:
Drag the Date field here
Drag the Category field here and put it under the Date field

DATA: Drag the Name field here
If it doesn't list as Count of Name...dbl-click it and set it to Count
Click [OK]
Select where you want the Pivot Table...and you're done!

That will list each Date/Category combination used and the Count of Names.

To refresh the Pivot Table, just right click it and select Refresh Data

Post back with any questions.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"stratis" wrote:

I have a long 16000 lines sheeet that contains the checking per day per
Section.
ie
Jan 1, Sales, Mr X
Jan 1, Sales, Mr Y
Jan 1, Marketing, Mr Z
Jan 2, Sales, Mr X
Jan 2, Marketing, Mr Z

I want to add the functiion like COunt, or dcount
Jan 1, Sales 2, people
Jan1, Marketing, 1 person
Jan 2, Sales, 1 Person
etc...

Unfortunately it does not work
As thery checking we get a record that has the date the dept and the person.
I need to have one entry per date that has consolidated the number of people
that checkin .
It seems that DcountA is the function but I cannot figure out how it works


"fluffymoore" wrote:

=COUNT(CELL RANGE)

or

= COUNTA(CELL RANGE)

"stratis" wrote:

I have a long 16000 lines sheeet that contains the checking per day per
Section.
ie
Jan 1, Sales, Mr X
Jan 1, Sales, Mr Y
Jan 1, Marketing, Mr Z
Jan 2, Sales, Mr X
Jan 2, Marketing, Mr Z

I want to add the functiion like COunt, or dcount
Jan 1, Sales 2, people
Jan1, Marketing, 1 person
Jan 2, Sales, 1 Person
etc...

Thanks but I do not want to use a pivot table,
The reason is that in reality We are talking of 75 different files that all
checking are getting registered, and thgis is the management view, If I make
a pivot table for each one of those files
then How I will be able to consolidate all date in one file. I have to open
each one of them every day and run update to get the update report.

A1 B1 C1 D1 E1
A2 DATE DEPT PERSON
A3 JAN1 SALES A
A4 JAN1 SALES B
A5 JAN1 SALES A
A6 JAN4 MKTG A
A7 etc
A8 TOTAL JAN1 JAN2 JAN3 JAN4
A9 SALES 3 0 0 0
A10 MKTG 0 0 0 1

"Ron Coderre" wrote:

A Pivot Table is probably the easiest approach....
Just make sure you have column headings
(I'll assume: Date,Category,Name)

From the Excel Main Menu....
<Data<Pivot Table
Use: Excel
Select your data
Click the [Layout] button

ROW:
Drag the Date field here
Drag the Category field here and put it under the Date field

DATA: Drag the Name field here
If it doesn't list as Count of Name...dbl-click it and set it to Count
Click [OK]
Select where you want the Pivot Table...and you're done!

That will list each Date/Category combination used and the Count of Names.

To refresh the Pivot Table, just right click it and select Refresh Data

Post back with any questions.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"stratis" wrote:

I have a long 16000 lines sheeet that contains the checking per day per
Section.
ie
Jan 1, Sales, Mr X
Jan 1, Sales, Mr Y
Jan 1, Marketing, Mr Z
Jan 2, Sales, Mr X
Jan 2, Marketing, Mr Z

I want to add the functiion like COunt, or dcount
Jan 1, Sales 2, people
Jan1, Marketing, 1 person
Jan 2, Sales, 1 Person
etc...

Now, stratis....In case it didn't occur to you before..there is usually a
HUGE difference between the solution you'll get to summarize one worksheet
and the one you'll get to summarize SEVENTY FIVE DIFFERENT FILES!. That's
the kind of detail you should mention right up front.

If what you described needs to be done on a regular basis, then it seems
that what you have is a project that somehow got out of hand. I'll assume
that's your situation and offer this approach to consider:

First, assuming Excel is the preferred host application, each of the 75
files should be created from a single common template that makes juducious
use of range names, data validation, and protection. I would create a vba
driven model that cycles through a list and creates the 75 files
automatically, pre-populating any key information that the users need and to
facilitate data consolidation.

Second, you need a mechanism to harvest the data from the 75 files into one
table or data list. I'd go with a vba driven MS Access model for that, but
you could conceivably do it with Excel, too.

Third, once all of the data is in one table, your options are wide open for
transforming that data into the kind of schema that facilitates summarization
and specialized reporting.

Last, you'll need a reporting and analysis model dedicated to summarizing
and reporting the data. Either Excel or MS Access would likely work.

So, what I'm talking about is a bonafide project....the kind of work that
many of us do professionally.

If I'm wrong about your situation, then please post the missing details that
this group will need to assist you. Either way, though, these forums are the
best place to get the help you need.

***********
Regards,
Ron

XL2002, WinXP


"stratis" wrote:


Thanks but I do not want to use a pivot table,
The reason is that in reality We are talking of 75 different files that all
checking are getting registered, and thgis is the management view, If I make
a pivot table for each one of those files
then How I will be able to consolidate all date in one file. I have to open
each one of them every day and run update to get the update report.

A1 B1 C1 D1 E1
A2 DATE DEPT PERSON
A3 JAN1 SALES A
A4 JAN1 SALES B
A5 JAN1 SALES A
A6 JAN4 MKTG A
A7 etc
A8 TOTAL JAN1 JAN2 JAN3 JAN4
A9 SALES 3 0 0 0
A10 MKTG 0 0 0 1

"Ron Coderre" wrote:

A Pivot Table is probably the easiest approach....
Just make sure you have column headings
(I'll assume: Date,Category,Name)

From the Excel Main Menu....
<Data<Pivot Table
Use: Excel
Select your data
Click the [Layout] button

ROW:
Drag the Date field here
Drag the Category field here and put it under the Date field

DATA: Drag the Name field here
If it doesn't list as Count of Name...dbl-click it and set it to Count
Click [OK]
Select where you want the Pivot Table...and you're done!

That will list each Date/Category combination used and the Count of Names.

To refresh the Pivot Table, just right click it and select Refresh Data

Post back with any questions.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"stratis" wrote:

I have a long 16000 lines sheeet that contains the checking per day per
Section.
ie
Jan 1, Sales, Mr X
Jan 1, Sales, Mr Y
Jan 1, Marketing, Mr Z
Jan 2, Sales, Mr X
Jan 2, Marketing, Mr Z

I want to add the functiion like COunt, or dcount
Jan 1, Sales 2, people
Jan1, Marketing, 1 person
Jan 2, Sales, 1 Person
etc...

I guess my option now that i have better understanding is to create in each
file a pivot and have them a pivot of pivots. I am not sure how this can
happen.


"Ron Coderre" wrote:

Now, stratis....In case it didn't occur to you before..there is usually a
HUGE difference between the solution you'll get to summarize one worksheet
and the one you'll get to summarize SEVENTY FIVE DIFFERENT FILES!. That's
the kind of detail you should mention right up front.

If what you described needs to be done on a regular basis, then it seems
that what you have is a project that somehow got out of hand. I'll assume
that's your situation and offer this approach to consider:

First, assuming Excel is the preferred host application, each of the 75
files should be created from a single common template that makes juducious
use of range names, data validation, and protection. I would create a vba
driven model that cycles through a list and creates the 75 files
automatically, pre-populating any key information that the users need and to
facilitate data consolidation.

Second, you need a mechanism to harvest the data from the 75 files into one
table or data list. I'd go with a vba driven MS Access model for that, but
you could conceivably do it with Excel, too.

Third, once all of the data is in one table, your options are wide open for
transforming that data into the kind of schema that facilitates summarization
and specialized reporting.

Last, you'll need a reporting and analysis model dedicated to summarizing
and reporting the data. Either Excel or MS Access would likely work.

So, what I'm talking about is a bonafide project....the kind of work that
many of us do professionally.

If I'm wrong about your situation, then please post the missing details that
this group will need to assist you. Either way, though, these forums are the
best place to get the help you need.

***********
Regards,
Ron

XL2002, WinXP


"stratis" wrote:


Thanks but I do not want to use a pivot table,
The reason is that in reality We are talking of 75 different files that all
checking are getting registered, and thgis is the management view, If I make
a pivot table for each one of those files
then How I will be able to consolidate all date in one file. I have to open
each one of them every day and run update to get the update report.

A1 B1 C1 D1 E1
A2 DATE DEPT PERSON
A3 JAN1 SALES A
A4 JAN1 SALES B
A5 JAN1 SALES A
A6 JAN4 MKTG A
A7 etc
A8 TOTAL JAN1 JAN2 JAN3 JAN4
A9 SALES 3 0 0 0
A10 MKTG 0 0 0 1

"Ron Coderre" wrote:

A Pivot Table is probably the easiest approach....
Just make sure you have column headings
(I'll assume: Date,Category,Name)

From the Excel Main Menu....
<Data<Pivot Table
Use: Excel
Select your data
Click the [Layout] button

ROW:
Drag the Date field here
Drag the Category field here and put it under the Date field

DATA: Drag the Name field here
If it doesn't list as Count of Name...dbl-click it and set it to Count
Click [OK]
Select where you want the Pivot Table...and you're done!

That will list each Date/Category combination used and the Count of Names.

To refresh the Pivot Table, just right click it and select Refresh Data

Post back with any questions.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"stratis" wrote:

I have a long 16000 lines sheeet that contains the checking per day per
Section.
ie
Jan 1, Sales, Mr X
Jan 1, Sales, Mr Y
Jan 1, Marketing, Mr Z
Jan 2, Sales, Mr X
Jan 2, Marketing, Mr Z

I want to add the functiion like COunt, or dcount
Jan 1, Sales 2, people
Jan1, Marketing, 1 person
Jan 2, Sales, 1 Person
etc...