Looking for a solution
 

I'm fairly comfortable around Excel with formulas and pivot tables but have
encountered an opportunity I have not yet faced. I have a spreadsheet with 3
tabs. The first tab contains HR information and has one row per employee.
The second tab contains Util Hrs and can have up to three rows per employee.
The Third tab contains Hrs Worked and can contain up to thirteen rows per
employee. I need to summarize the information in the 2nd and 3rd tabs to
return a single row of additional information back to the 1st tab (which I
can do using the LOOKUP function).
What I don't know how to do is apply a formula to the 2nd and 3rd tabs to
give me a unique value per employee. Below is an example of the data:

Tab 1 Tab 2 Tab 3
Col A Col A Col B Col C Col A
Col B Col C
ANDPE01 ANDPE01 176 22.5 ANDPE01 40
39.5
ANDPE01 160 30 ANDPE01
40 40
ANDPE01 160 64.5 ANDPE01 40
32

ANDPE01 40 37.5

What I expect to be returned to Tab 1 is:

For ANDPE01 Col A= SUM(Tab2:Col C) / SUM(Tab2:Col B) which would be .2358
Col B=SUM(Tab3:Col C) / SUM(Tab3:Col B) which would be
..9312

Any suggestions? I have approximately 2500 rows in the first tab that need
to have this consolidation performed. Thanks

Looking for a solution
 

I'm afraid your data got a little skewed when you posted. However, based on
the last bit where you're stating what you want, it sounds like you need to
do a couple of sums based on a name. In which case, maybe something like:

=SUMIF('Tab 2'!A:A,A2,C:C)/SUMIF('Tab 2'!A:A,A2,C:C)

and

=SUMIF('Tab 3'!A:A,A2,C:C)/SUMIF('Tab 3'!A:A,A2,C:C)

If this isn't what your looking for, perhaps retry posting your data
(organize downward, avoid going across, if possible).
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"CA Linda" wrote:

I'm fairly comfortable around Excel with formulas and pivot tables but have
encountered an opportunity I have not yet faced. I have a spreadsheet with 3
tabs. The first tab contains HR information and has one row per employee.
The second tab contains Util Hrs and can have up to three rows per employee.
The Third tab contains Hrs Worked and can contain up to thirteen rows per
employee. I need to summarize the information in the 2nd and 3rd tabs to
return a single row of additional information back to the 1st tab (which I
can do using the LOOKUP function).
What I don't know how to do is apply a formula to the 2nd and 3rd tabs to
give me a unique value per employee. Below is an example of the data:

Tab 1 Tab 2 Tab 3
Col A Col A Col B Col C Col A
Col B Col C
ANDPE01 ANDPE01 176 22.5 ANDPE01 40
39.5
ANDPE01 160 30 ANDPE01
40 40
ANDPE01 160 64.5 ANDPE01 40
32

ANDPE01 40 37.5

What I expect to be returned to Tab 1 is:

For ANDPE01 Col A= SUM(Tab2:Col C) / SUM(Tab2:Col B) which would be .2358
Col B=SUM(Tab3:Col C) / SUM(Tab3:Col B) which would be
.9312

Any suggestions? I have approximately 2500 rows in the first tab that need
to have this consolidation performed. Thanks