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????