Formula Needed

Closed
boderek - Feb 21, 2012 at 09:53 AM
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
- Feb 23, 2012 at 08:41 AM
Hello,
Hi - needing some help with a formula - I have several columns taken from sage data. I have in one column employees hours and another employees name, as you can see sometimes employees name appears more than once. what I am needing is in another row is employee name who was working on the job to appear and all their hours beside their name. Would be greatful for your help

column A column B column C column D
HOURS RATE COST EMPLOYEE

13.5 £8.00 £108.00 L ANTCZAK
0.5 £6.85 £3.43 JA PEACOCK
0.5 £6.85 £3.43 JA PEACOCK
1.5 £6.75 £10.13 P OLENIACZ
1 £6.75 £6.75 P OLENIACZ
17 £131.74



3 replies

Thought I'd try to make my question a bit clearer

Hi - needing some help with a formula - I have several columns taken from sage data. I have in one column employees hours (A) and another employees name(D), as you can see sometimes employees name appears more than once. what I am needing is in another row is employee name who was working on the job to appear and all their hours beside their name. Would be greatful for your help
A B C D
hours rate cost employee
13.5 £8.00 £108.00 SMITH
0.5 £6.85 £3.43 JONES
0.5 £6.85 £3.43 JONES
1.5 £6.75 £10.13 SMITH
1 £6.75 £6.75 SMITH
17 £131.74


employee total hours

SMITH 16
JONES 1
0
Columns aren't copying over very well..

column A hours
Column B hours
Column C Costs
Column D Employee

Want to search column A & D then list Employee name and total hours for that employee.

Hope this is a bit clearer???

Thanks
0
RWomanizer
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Feb 23, 2012 at 05:38 AM
Use pivot table,

drag employee name in row label and total hours in values.
0
Hi - Thanks for replying

Have set up report from data transferred from sage on same worksheet - so would like to keep all breakdown of data on same worksheet if possible.
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Feb 23, 2012 at 08:41 AM
Hi Boderek,

Try using an array formula.

Your given table:
13.5 £8.00 £108.00 SMITH
0.5 £6.85 £3.43 JONES
0.5 £6.85 £3.43 JONES
1.5 £6.75 £10.13 SMITH
1 £6.75 £6.75 SMITH

Is located in range A1:D5.

Then use the following formula anywhere on the sheet:
=SUM(($D$1:$D$5="SMITH")*($A$1:$A$5))
An array formula needs to be confirmed by hitting Ctrl+Shift+Enter.
Doing this correctly will enclose the formula in curly brackets.

Now drag the formula down and change the name SMITH into another. Don't forget to confirm as described above.

Best regards,
Trowa
0