I have a spreadsheet used to track student participation in different
activities offered. The main spreadsheet lists the names of the student,
their DOB, age, gender and race. If they participate in a particular
activity, an X is placed in the corresponding cell out past their name. A
separate sheet will contain statistical data for each activity.

The stats sheet needs to contain the following information:

Age breakdown by gender (i.e. x # of 4 year old boys)

The next calculation will tell % of participation based off race

I'm still searching the various post to find the answer, but any help before
I find the answer is greatly appreciated.

I'm going to suppose that your table is on the top left column, from rows 1
to 10, with headers, the race on the column B, the age on column C and the
gender on column D, just for the examples.

For the breakdown by gender you can create a custom column with the
concatenation of those two fields, like "=D2 & " (" & C2 & ")" to produce
"girl (4)" for example. Then use the same format for your statisticals list,
and use COUNTIF over it (for example, if you put Girl(4) in cell A15 and the
range with the concatenated fields is L2:L10 the formula on B15 will be
"=COUNTIF(L2:L10,A15))

If you don't mind having 1 instead of X then you can use SUMIF with the %
participation based on race.
First you can use SUM at the bottom of the activity table to get the total
number of participants (row 11)
Then create a list with all the races (starting in A20, for example) and use
SUMIF over the data origin, using the range where you have the race in the
activity table as the first parameter, the list that you have created as the
second, and the area with the 1s for each activity as the third. In this
example it will be "=SUMIF(B2:B10,A20,E2:E10)"
You can divide the sum you calculated before to get the % directly
"=SUMIF(B2:B10,A20,E2:E10) / E11"

--
It is nice to be important, but it is more important to be nice


"Millerk" wrote:

I have a spreadsheet used to track student participation in different
activities offered. The main spreadsheet lists the names of the student,
their DOB, age, gender and race. If they participate in a particular
activity, an X is placed in the corresponding cell out past their name. A
separate sheet will contain statistical data for each activity.

The stats sheet needs to contain the following information:

Age breakdown by gender (i.e. x # of 4 year old boys)

The next calculation will tell % of participation based off race

I'm still searching the various post to find the answer, but any help before
I find the answer is greatly appreciated.

Miguel,

I think you might have answered my question, but I'm having a little trouble
extracting the information out. I'll explain it a little better and
hopefully this helps.

My data is all on sheet 1 and the stats will be on sheet 2

Sheet 1
Age = Column C
Gender = Column D
Ethinicity = Column E

Sheet 2 (formulas I'm using)
Age =COUNTIF('Sheet 1'!C2:C2001,"4")
Gender =COUNTIF('Sheet 1'!C2:C2001,"M")
Ethinicity =SUMPRODUCT(--('Sheet 1'!E2:E2001="asian"),--('Sheet
1'!D2:D2001="M"))/COUNT('Sheet 1'!E2:E2001)

All of my formulas work except for the ethinicity. The example I have there
should produce the percent of male asians, but instead it gives me#DIV/0!
error. I've formated the cell for percentage, and still getting the error.
Is there something wrong with my formula or should I be using something else
to get this?



"Miguel Zapico" wrote:

I'm going to suppose that your table is on the top left column, from rows 1
to 10, with headers, the race on the column B, the age on column C and the
gender on column D, just for the examples.

For the breakdown by gender you can create a custom column with the
concatenation of those two fields, like "=D2 & " (" & C2 & ")" to produce
"girl (4)" for example. Then use the same format for your statisticals list,
and use COUNTIF over it (for example, if you put Girl(4) in cell A15 and the
range with the concatenated fields is L2:L10 the formula on B15 will be
"=COUNTIF(L2:L10,A15))

If you don't mind having 1 instead of X then you can use SUMIF with the %
participation based on race.
First you can use SUM at the bottom of the activity table to get the total
number of participants (row 11)
Then create a list with all the races (starting in A20, for example) and use
SUMIF over the data origin, using the range where you have the race in the
activity table as the first parameter, the list that you have created as the
second, and the area with the 1s for each activity as the third. In this
example it will be "=SUMIF(B2:B10,A20,E2:E10)"
You can divide the sum you calculated before to get the % directly
"=SUMIF(B2:B10,A20,E2:E10) / E11"

--
It is nice to be important, but it is more important to be nice


"Millerk" wrote:

I have a spreadsheet used to track student participation in different
activities offered. The main spreadsheet lists the names of the student,
their DOB, age, gender and race. If they participate in a particular
activity, an X is placed in the corresponding cell out past their name. A
separate sheet will contain statistical data for each activity.

The stats sheet needs to contain the following information:

Age breakdown by gender (i.e. x # of 4 year old boys)

The next calculation will tell % of participation based off race

I'm still searching the various post to find the answer, but any help before
I find the answer is greatly appreciated.

The error is in the COUNT function over non numeric cells, use COUNTA instead
--
It is nice to be important, but it is more important to be nice


"Millerk" wrote:

Miguel,

I think you might have answered my question, but I'm having a little trouble
extracting the information out. I'll explain it a little better and
hopefully this helps.

My data is all on sheet 1 and the stats will be on sheet 2

Sheet 1
Age = Column C
Gender = Column D
Ethinicity = Column E

Sheet 2 (formulas I'm using)
Age =COUNTIF('Sheet 1'!C2:C2001,"4")
Gender =COUNTIF('Sheet 1'!C2:C2001,"M")
Ethinicity =SUMPRODUCT(--('Sheet 1'!E2:E2001="asian"),--('Sheet
1'!D2:D2001="M"))/COUNT('Sheet 1'!E2:E2001)

All of my formulas work except for the ethinicity. The example I have there
should produce the percent of male asians, but instead it gives me#DIV/0!
error. I've formated the cell for percentage, and still getting the error.
Is there something wrong with my formula or should I be using something else
to get this?



"Miguel Zapico" wrote:

I'm going to suppose that your table is on the top left column, from rows 1
to 10, with headers, the race on the column B, the age on column C and the
gender on column D, just for the examples.

For the breakdown by gender you can create a custom column with the
concatenation of those two fields, like "=D2 & " (" & C2 & ")" to produce
"girl (4)" for example. Then use the same format for your statisticals list,
and use COUNTIF over it (for example, if you put Girl(4) in cell A15 and the
range with the concatenated fields is L2:L10 the formula on B15 will be
"=COUNTIF(L2:L10,A15))

If you don't mind having 1 instead of X then you can use SUMIF with the %
participation based on race.
First you can use SUM at the bottom of the activity table to get the total
number of participants (row 11)
Then create a list with all the races (starting in A20, for example) and use
SUMIF over the data origin, using the range where you have the race in the
activity table as the first parameter, the list that you have created as the
second, and the area with the 1s for each activity as the third. In this
example it will be "=SUMIF(B2:B10,A20,E2:E10)"
You can divide the sum you calculated before to get the % directly
"=SUMIF(B2:B10,A20,E2:E10) / E11"

--
It is nice to be important, but it is more important to be nice


"Millerk" wrote:

I have a spreadsheet used to track student participation in different
activities offered. The main spreadsheet lists the names of the student,
their DOB, age, gender and race. If they participate in a particular
activity, an X is placed in the corresponding cell out past their name. A
separate sheet will contain statistical data for each activity.

The stats sheet needs to contain the following information:

Age breakdown by gender (i.e. x # of 4 year old boys)

The next calculation will tell % of participation based off race

I'm still searching the various post to find the answer, but any help before
I find the answer is greatly appreciated.

Thanks, works like a charm

"Miguel Zapico" wrote:

The error is in the COUNT function over non numeric cells, use COUNTA instead
--
It is nice to be important, but it is more important to be nice


"Millerk" wrote:

Miguel,

I think you might have answered my question, but I'm having a little trouble
extracting the information out. I'll explain it a little better and
hopefully this helps.

My data is all on sheet 1 and the stats will be on sheet 2

Sheet 1
Age = Column C
Gender = Column D
Ethinicity = Column E

Sheet 2 (formulas I'm using)
Age =COUNTIF('Sheet 1'!C2:C2001,"4")
Gender =COUNTIF('Sheet 1'!C2:C2001,"M")
Ethinicity =SUMPRODUCT(--('Sheet 1'!E2:E2001="asian"),--('Sheet
1'!D2:D2001="M"))/COUNT('Sheet 1'!E2:E2001)

All of my formulas work except for the ethinicity. The example I have there
should produce the percent of male asians, but instead it gives me#DIV/0!
error. I've formated the cell for percentage, and still getting the error.
Is there something wrong with my formula or should I be using something else
to get this?



"Miguel Zapico" wrote:

I'm going to suppose that your table is on the top left column, from rows 1
to 10, with headers, the race on the column B, the age on column C and the
gender on column D, just for the examples.

For the breakdown by gender you can create a custom column with the
concatenation of those two fields, like "=D2 & " (" & C2 & ")" to produce
"girl (4)" for example. Then use the same format for your statisticals list,
and use COUNTIF over it (for example, if you put Girl(4) in cell A15 and the
range with the concatenated fields is L2:L10 the formula on B15 will be
"=COUNTIF(L2:L10,A15))

If you don't mind having 1 instead of X then you can use SUMIF with the %
participation based on race.
First you can use SUM at the bottom of the activity table to get the total
number of participants (row 11)
Then create a list with all the races (starting in A20, for example) and use
SUMIF over the data origin, using the range where you have the race in the
activity table as the first parameter, the list that you have created as the
second, and the area with the 1s for each activity as the third. In this
example it will be "=SUMIF(B2:B10,A20,E2:E10)"
You can divide the sum you calculated before to get the % directly
"=SUMIF(B2:B10,A20,E2:E10) / E11"

--
It is nice to be important, but it is more important to be nice


"Millerk" wrote:

I have a spreadsheet used to track student participation in different
activities offered. The main spreadsheet lists the names of the student,
their DOB, age, gender and race. If they participate in a particular
activity, an X is placed in the corresponding cell out past their name. A
separate sheet will contain statistical data for each activity.

The stats sheet needs to contain the following information:

Age breakdown by gender (i.e. x # of 4 year old boys)

The next calculation will tell % of participation based off race

I'm still searching the various post to find the answer, but any help before
I find the answer is greatly appreciated.