-3
Month   Employee    Cost
Sept    Emp 1    £1.10
Sept    Emp 2   £15.00
Sept    Emp 3   £11.88
Sept    Emp 3    £8.52
Oct     Emp 3   £20.73
Oct     Emp 3   £58.33
Oct     Emp 2    £2.00
Oct     Emp 1   £38.02
Oct     Emp 2    £7.81

I am trying to return a cost value for each employee by month occurred.

The desired result, based on the extract above, is: 

Sept
Emp 1  £1.10
Emp 2 £15.00
Emp 3 £20.40

Oct
Emp 1 £38.02
Emp 2  £9.81
Emp 3 £79.06

I have been using a SUMIF function but can only total up the items for each employee, not also extract the data for a particular month.

I am at a loss and could desperately use some help.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Rich Burrell
  • 9

2 Answers2

1

You can make use of pivot tables to do this quickly:

  1. Select the table, go to Insert > Insert Pivot Tables

  2. Click OK then drag the field Month, then Employee to the box labelled 'Row labels'

  3. Drag the field 'Cost' into the box named 'Values'.

You can then format the table as Tabular Form and remove subtotals (both under the tab menu PivotTable Options) to get something like this:

enter image description here

Jerry
  • 70,495
  • 13
  • 100
  • 144
0

Is the month data just text like "Sept" or "Oct" rather than date values?

In Excel 2003 you can use SUMPRODUCT function for multi-conditional summing or counting, e.g. assuming Month in A2:A10, Employee in B2:B10 and cost in C2:C10

=SUMPRODUCT((A$2:A$10=E2)*(B$2:B$10=F2),C$2:C$10)

where E2 contains a specific month and F2 a specific employee

barry houdini
  • 45,615
  • 8
  • 63
  • 81