Formula Needed

[Closed]
Report
-
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
-
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
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
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Use pivot table,

drag employee name in row label and total hours in values.
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.
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
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