Formula Help. Sum of Cells if Certain Criteria is Met

[miketav99]

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]

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)

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