Best way to total scattered data?
 

I've got a worksheet that I need to grab total numbers off of but I don't
know how to separate them out the way the sheet is currently set up. Right
now it looks something like:

Client Name: Signup Date: Counselor
Janet 10-24-07 Mike
Bill 10-24-07 Mike
Evan 10-26-07 Steve
Lucy 10-27-07 Alice

Is it possible for Excel to gather the fact that MIKE has 2 enrollments and
STEVE and ALICE each have 1?

Best way to total scattered data?
 

Sort the data by Counselor and then do the following

Click DATA in the menu and select SUBTOTALS

In the dialog box, set the following values:

AT EACH CHANGE IN set to Counselor
USE FUNCTION set to Count
Click the Check box for the Client Name field as the item to count
Click OK to generate subtotals.

The count per counselor appears after each name grouping and an overall
count of client names appears on the last line of the subtotals.
--
Kevin Backmann


"Angyl" wrote:

I've got a worksheet that I need to grab total numbers off of but I don't
know how to separate them out the way the sheet is currently set up. Right
now it looks something like:

Client Name: Signup Date: Counselor
Janet 10-24-07 Mike
Bill 10-24-07 Mike
Evan 10-26-07 Steve
Lucy 10-27-07 Alice

Is it possible for Excel to gather the fact that MIKE has 2 enrollments and
STEVE and ALICE each have 1?

Best way to total scattered data?
 

Thanks, Kevin, that did it!

One more question: I know how to copy data from one cell to another
worksheet using the =(sum [range]) feature, but is there a quick way to do
that with an entire worksheet.

What you've showed me works, and I'm making a chart out of the resultant
data, but it does kind of "uglify" the original worksheet, so what I'd like
to do is actually have TWO duplicate worksheets in the book, one looking
"Normal" and not sorted and the other one drawing the data from that and
sorting it so that I can use the results to make my chart.

"Kevin B" wrote:

Sort the data by Counselor and then do the following

Click DATA in the menu and select SUBTOTALS

In the dialog box, set the following values:

AT EACH CHANGE IN set to Counselor
USE FUNCTION set to Count
Click the Check box for the Client Name field as the item to count
Click OK to generate subtotals.

The count per counselor appears after each name grouping and an overall
count of client names appears on the last line of the subtotals.
--
Kevin Backmann


"Angyl" wrote:

I've got a worksheet that I need to grab total numbers off of but I don't
know how to separate them out the way the sheet is currently set up. Right
now it looks something like:

Client Name: Signup Date: Counselor
Janet 10-24-07 Mike
Bill 10-24-07 Mike
Evan 10-26-07 Steve
Lucy 10-27-07 Alice

Is it possible for Excel to gather the fact that MIKE has 2 enrollments and
STEVE and ALICE each have 1?

Best way to total scattered data?
 

Right-click on the sheet tab that you want to copy and select MOVE OR COPY...

Indicate in the BEFORE SHEET list box, where you would like to copy to be
placed, or select the last option (MOVE TO END) to park out back.

Then click the CREATE A COPY check box.

Failure to click the check box will just move your workbook to the end, but
nothing terrible happens.

The new worksheet has the same name as the original, followed by an
increment number.

--
Kevin Backmann


"Angyl" wrote:

Thanks, Kevin, that did it!

One more question: I know how to copy data from one cell to another
worksheet using the =(sum [range]) feature, but is there a quick way to do
that with an entire worksheet.

What you've showed me works, and I'm making a chart out of the resultant
data, but it does kind of "uglify" the original worksheet, so what I'd like
to do is actually have TWO duplicate worksheets in the book, one looking
"Normal" and not sorted and the other one drawing the data from that and
sorting it so that I can use the results to make my chart.

"Kevin B" wrote:

Sort the data by Counselor and then do the following

Click DATA in the menu and select SUBTOTALS

In the dialog box, set the following values:

AT EACH CHANGE IN set to Counselor
USE FUNCTION set to Count
Click the Check box for the Client Name field as the item to count
Click OK to generate subtotals.

The count per counselor appears after each name grouping and an overall
count of client names appears on the last line of the subtotals.
--
Kevin Backmann


"Angyl" wrote:

I've got a worksheet that I need to grab total numbers off of but I don't
know how to separate them out the way the sheet is currently set up. Right
now it looks something like:

Client Name: Signup Date: Counselor
Janet 10-24-07 Mike
Bill 10-24-07 Mike
Evan 10-26-07 Steve
Lucy 10-27-07 Alice

Is it possible for Excel to gather the fact that MIKE has 2 enrollments and
STEVE and ALICE each have 1?

Best way to total scattered data?
 

You can skip the extra sheet & the subtotals by doing the following

Place the counselor names down a column, one counselor per row and the use
the following formula to get your counts:
Column X Column Y
Mike | =COUNTIF(X1:C5,"=Mike")
Alice | =COUNTIF(C1:C5,"=Alice")
Steve | =COUNTIF(C1:C5,"=Steve")

It's neater and it will be easier to chart.

--
Kevin Backmann


"Kevin B" wrote:

Right-click on the sheet tab that you want to copy and select MOVE OR COPY...

Indicate in the BEFORE SHEET list box, where you would like to copy to be
placed, or select the last option (MOVE TO END) to park out back.

Then click the CREATE A COPY check box.

Failure to click the check box will just move your workbook to the end, but
nothing terrible happens.

The new worksheet has the same name as the original, followed by an
increment number.

--
Kevin Backmann


"Angyl" wrote:

Thanks, Kevin, that did it!

One more question: I know how to copy data from one cell to another
worksheet using the =(sum [range]) feature, but is there a quick way to do
that with an entire worksheet.

What you've showed me works, and I'm making a chart out of the resultant
data, but it does kind of "uglify" the original worksheet, so what I'd like
to do is actually have TWO duplicate worksheets in the book, one looking
"Normal" and not sorted and the other one drawing the data from that and
sorting it so that I can use the results to make my chart.

"Kevin B" wrote:

Sort the data by Counselor and then do the following

Click DATA in the menu and select SUBTOTALS

In the dialog box, set the following values:

AT EACH CHANGE IN set to Counselor
USE FUNCTION set to Count
Click the Check box for the Client Name field as the item to count
Click OK to generate subtotals.

The count per counselor appears after each name grouping and an overall
count of client names appears on the last line of the subtotals.
--
Kevin Backmann


"Angyl" wrote:

I've got a worksheet that I need to grab total numbers off of but I don't
know how to separate them out the way the sheet is currently set up. Right
now it looks something like:

Client Name: Signup Date: Counselor
Janet 10-24-07 Mike
Bill 10-24-07 Mike
Evan 10-26-07 Steve
Lucy 10-27-07 Alice

Is it possible for Excel to gather the fact that MIKE has 2 enrollments and
STEVE and ALICE each have 1?

Best way to total scattered data?
 

Mike, this formula example is PERFECT! YOU ABSOLUTELY ROCK!!

"Kevin B" wrote:

You can skip the extra sheet & the subtotals by doing the following

Place the counselor names down a column, one counselor per row and the use
the following formula to get your counts:
Column X Column Y
Mike | =COUNTIF(X1:C5,"=Mike")
Alice | =COUNTIF(C1:C5,"=Alice")
Steve | =COUNTIF(C1:C5,"=Steve")

It's neater and it will be easier to chart.

--
Kevin Backmann


"Kevin B" wrote:

Right-click on the sheet tab that you want to copy and select MOVE OR COPY...

Indicate in the BEFORE SHEET list box, where you would like to copy to be
placed, or select the last option (MOVE TO END) to park out back.

Then click the CREATE A COPY check box.

Failure to click the check box will just move your workbook to the end, but
nothing terrible happens.

The new worksheet has the same name as the original, followed by an
increment number.

--
Kevin Backmann


"Angyl" wrote:

Thanks, Kevin, that did it!

One more question: I know how to copy data from one cell to another
worksheet using the =(sum [range]) feature, but is there a quick way to do
that with an entire worksheet.

What you've showed me works, and I'm making a chart out of the resultant
data, but it does kind of "uglify" the original worksheet, so what I'd like
to do is actually have TWO duplicate worksheets in the book, one looking
"Normal" and not sorted and the other one drawing the data from that and
sorting it so that I can use the results to make my chart.

"Kevin B" wrote:

Sort the data by Counselor and then do the following

Click DATA in the menu and select SUBTOTALS

In the dialog box, set the following values:

AT EACH CHANGE IN set to Counselor
USE FUNCTION set to Count
Click the Check box for the Client Name field as the item to count
Click OK to generate subtotals.

The count per counselor appears after each name grouping and an overall
count of client names appears on the last line of the subtotals.
--
Kevin Backmann


"Angyl" wrote:

I've got a worksheet that I need to grab total numbers off of but I don't
know how to separate them out the way the sheet is currently set up. Right
now it looks something like:

Client Name: Signup Date: Counselor
Janet 10-24-07 Mike
Bill 10-24-07 Mike
Evan 10-26-07 Steve
Lucy 10-27-07 Alice

Is it possible for Excel to gather the fact that MIKE has 2 enrollments and
STEVE and ALICE each have 1?