# Formula Needed

Closed
boderek - Feb 21, 2012 at 09:53 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 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 responses

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

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.
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Feb 23, 2012 at 08:41 AM
Hi Boderek,

Try using an array formula.

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