I have a worksheet with 4465 rows of data. The data is sortable using the
list function. Any combination of sorting criteria brings back a different
set of results.

I want to be able to know the number of rows ( each row is a person) so that
I can see how many people out of the total number fall into a particular
category.

Even better would be a percentage, but by getting the number of people I
could manually convert to percentage.

Much appreciated for any adivce in advance.

Sumproduct is the best method

=Sumproduct(--(A1:A4465=Criteria1),--(B1:B4465=Criteria2),--(C1:C4465=Criteria3))


You can put as many or few criterias in the formula. to get percentage
simply divide by 4465 and format the cell with the formula to show percentage.

"Big Kahuna" wrote:

I have a worksheet with 4465 rows of data. The data is sortable using the
list function. Any combination of sorting criteria brings back a different
set of results.

I want to be able to know the number of rows ( each row is a person) so that
I can see how many people out of the total number fall into a particular
category.

Even better would be a percentage, but by getting the number of people I
could manually convert to percentage.

Much appreciated for any adivce in advance.

Is it possible for the cell which I have placed this function recalculate as
I sort? The minute I sorted the number went away. I want to see that number
change as I add criteria to the sorting.

Example:
Total # of People
Add criteria 400 people removed. See total left.
Add criteria again 800 people removed. See total left.

"Joel" wrote:

Sumproduct is the best method

=Sumproduct(--(A1:A4465=Criteria1),--(B1:B4465=Criteria2),--(C1:C4465=Criteria3))


You can put as many or few criterias in the formula. to get percentage
simply divide by 4465 and format the cell with the formula to show percentage.

"Big Kahuna" wrote:

I have a worksheet with 4465 rows of data. The data is sortable using the
list function. Any combination of sorting criteria brings back a different
set of results.

I want to be able to know the number of rows ( each row is a person) so that
I can see how many people out of the total number fall into a particular
category.

Even better would be a percentage, but by getting the number of people I
could manually convert to percentage.

Much appreciated for any adivce in advance.

If you sort ranges is the same as the formula Range (A1:A4465,B:B4465, an
C1:C4465) there should be no problem with the sorting. The three criterias
shouldn't be in the sort range.

"Big Kahuna" wrote:

Is it possible for the cell which I have placed this function recalculate as
I sort? The minute I sorted the number went away. I want to see that number
change as I add criteria to the sorting.

Example:
Total # of People
Add criteria 400 people removed. See total left.
Add criteria again 800 people removed. See total left.

"Joel" wrote:

Sumproduct is the best method

=Sumproduct(--(A1:A4465=Criteria1),--(B1:B4465=Criteria2),--(C1:C4465=Criteria3))


You can put as many or few criterias in the formula. to get percentage
simply divide by 4465 and format the cell with the formula to show percentage.

"Big Kahuna" wrote:

I have a worksheet with 4465 rows of data. The data is sortable using the
list function. Any combination of sorting criteria brings back a different
set of results.

I want to be able to know the number of rows ( each row is a person) so that
I can see how many people out of the total number fall into a particular
category.

Even better would be a percentage, but by getting the number of people I
could manually convert to percentage.

Much appreciated for any adivce in advance.

First of all, I don't think you are "sorting".

Sounds like you are "filtering".

See help on the SUBTOTAL function to get a count of filtered items.


Gord Dibben MS Excel MVP

On Fri, 20 Feb 2009 07:07:01 -0800, Big Kahuna <Big
wrote:

I have a worksheet with 4465 rows of data. The data is sortable using the
list function. Any combination of sorting criteria brings back a different
set of results.

I want to be able to know the number of rows ( each row is a person) so that
I can see how many people out of the total number fall into a particular
category.

Even better would be a percentage, but by getting the number of people I
could manually convert to percentage.

Much appreciated for any adivce in advance.

I was thinkning the same thing, If you right click on the gray bar at the
bottom of Excell the menu pops up in which you can click "count".
now after you have filtered/sorted your data, highlight all the maching
entries, i.e. click the top cel then hold down CTRL and Shift then press the
down arrow. Now the gray bar at the bottom shows the count of all cells
highlighted.

"Gord Dibben" wrote:

First of all, I don't think you are "sorting".

Sounds like you are "filtering".

See help on the SUBTOTAL function to get a count of filtered items.


Gord Dibben MS Excel MVP

On Fri, 20 Feb 2009 07:07:01 -0800, Big Kahuna <Big
wrote:

I have a worksheet with 4465 rows of data. The data is sortable using the
list function. Any combination of sorting criteria brings back a different
set of results.

I want to be able to know the number of rows ( each row is a person) so that
I can see how many people out of the total number fall into a particular
category.

Even better would be a percentage, but by getting the number of people I
could manually convert to percentage.

Much appreciated for any adivce in advance.