Subtotals & Filters
 

I have a spreadsheet of data.
Column A contains "Group Names"
Column B has "the people within that group"
Columns C thru J contians the number of hours each individual has worked
Week 1, Week 2, Week 3, etc...

I have created filters on every column... If I filter on column A it will
provide me with those people who only work within that group in column B, and
their respecitve hours worked.

My question... I have a formula at the bottom of the spreadsheet that will
give me the total # of hours worked depending upon what I have the filter set
to. The formula is SUBTOTAL(1,L1:L1132). What I would like to do is create
another formula below this one which will hold the TOTAL GROUP HOURS
worked... even when I filter down to an individual person in Column B - so
that I can see the "PERCENTAGE" of hours an individual worked in relationship
to the group.

Any suggestions????

have a look in HELP index for SUMIF

--
Don Guillett
SalesAid Software

"BAM718" wrote in message
...
I have a spreadsheet of data.
Column A contains "Group Names"
Column B has "the people within that group"
Columns C thru J contians the number of hours each individual has worked
Week 1, Week 2, Week 3, etc...

I have created filters on every column... If I filter on column A it will
provide me with those people who only work within that group in column B,
and
their respecitve hours worked.

My question... I have a formula at the bottom of the spreadsheet that
will
give me the total # of hours worked depending upon what I have the filter
set
to. The formula is SUBTOTAL(1,L1:L1132). What I would like to do is
create
another formula below this one which will hold the TOTAL GROUP HOURS
worked... even when I filter down to an individual person in Column B - so
that I can see the "PERCENTAGE" of hours an individual worked in
relationship
to the group.

Any suggestions????

I am still confused...

This is the example...
Column A, Column B, Column C
Group Name Person Name Hours Worked
Green Team Jim 15
Green Team Bob 40
Green Team Harry 38
Red Team Mike 46
Red Team Joe 54
Red Team Rob 20

I want to Filter on the Green team. I then want to filter on Bob. I then
want to know what % of the Green Teams hours Bob represents.
This is the formula that I have... SUBTOTAL(1,L1:L1132). It provides me
with a subtotal for whatever names are visible. In this case... Bob and his
hours. That's fine. But I also need a formula below this one to provide me
with the total number of hours on the green team... hours and persons who are
NOT "visible" when I filter on Bob.

Does this make more sense?? Thanks.







"BAM718" wrote:

I have a spreadsheet of data.
Column A contains "Group Names"
Column B has "the people within that group"
Columns C thru J contians the number of hours each individual has worked
Week 1, Week 2, Week 3, etc...

I have created filters on every column... If I filter on column A it will
provide me with those people who only work within that group in column B, and
their respecitve hours worked.

My question... I have a formula at the bottom of the spreadsheet that will
give me the total # of hours worked depending upon what I have the filter set
to. The formula is SUBTOTAL(1,L1:L1132). What I would like to do is create
another formula below this one which will hold the TOTAL GROUP HOURS
worked... even when I filter down to an individual person in Column B - so
that I can see the "PERCENTAGE" of hours an individual worked in relationship
to the group.

Any suggestions????

without filtering at all
=SUMIF(a2:a7,"green",c2:c7)
to get green team=93
=SUMPRODUCT((a2:a7="green")*(b2:b7="Bob")*c2:c7)
=40 to get bob
then just put together in one formula and divide 1st by second =43%

--
Don Guillett
SalesAid Software

"BAM718" wrote in message
...
I am still confused...

This is the example...
Column A, Column B, Column C
Group Name Person Name Hours Worked
Green Team Jim 15
Green Team Bob 40
Green Team Harry 38
Red Team Mike 46
Red Team Joe 54
Red Team Rob 20

I want to Filter on the Green team. I then want to filter on Bob. I then
want to know what % of the Green Teams hours Bob represents.
This is the formula that I have... SUBTOTAL(1,L1:L1132). It provides me
with a subtotal for whatever names are visible. In this case... Bob and
his
hours. That's fine. But I also need a formula below this one to provide
me
with the total number of hours on the green team... hours and persons who
are
NOT "visible" when I filter on Bob.

Does this make more sense?? Thanks.







"BAM718" wrote:

I have a spreadsheet of data.
Column A contains "Group Names"
Column B has "the people within that group"
Columns C thru J contians the number of hours each individual has worked
Week 1, Week 2, Week 3, etc...

I have created filters on every column... If I filter on column A it
will
provide me with those people who only work within that group in column
B, and
their respecitve hours worked.

My question... I have a formula at the bottom of the spreadsheet that
will
give me the total # of hours worked depending upon what I have the
filter set
to. The formula is SUBTOTAL(1,L1:L1132). What I would like to do is
create
another formula below this one which will hold the TOTAL GROUP HOURS
worked... even when I filter down to an individual person in Column B -
so
that I can see the "PERCENTAGE" of hours an individual worked in
relationship
to the group.

Any suggestions????