Formula Help. Sum of Cells if Certain Criteria is Met

miketav99

New member
Joined
Dec 2, 2013
Messages
1
Reaction score
0
Points
0
Hellow everyone. Sorry if this has been answered already but I cannot find it. I am trying to create a formula that will search for employee numbers and add their sales value and spent values and report it on a seperate sheet which will contain a summary of all employees. So the formula would find all references to employee 1234 add their sales and spent columns and report "1234 Sales=1,500 and Spent=700.00". Thanks for your help.

A
B
C
D
E
1
Time
Date
Employee
Sales
Spent
2
1100
1 Jan 13
1234
1,000.00
500.00
3
1200
1 Jan 13
5678
800.00
300.00
4
0900
2 jan 13
1234
500.00
200.00
5
1400
2 Jan 13
5678
200.00
100.00
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Use Sumif

if your Summary sheet contains, employees down the left side, starting at A2, and you have 2 column headers starting at B1 labeled Sales and Spent, then formula would be:

=SUMIF('Sheet1'!$C:$C,$A2,'Sheet1'!D:D)

copied down and to next column.

Alternatively, you can use a Pivot Table, then drag Employee to Row label area, and Sales and Spent both get dragged to the Summation Area. This will autopopulate the unique employees and the respective sums.

Tutorial on Pivot Tables: http://office.microsoft.com/en-ca/excel-help/pivottable-reports-101-HA001034632.aspx
 
Top