# Thread: Formula Help. Sum of Cells if Certain Criteria is Met

1. ## Formula Help. Sum of Cells if Certain Criteria is Met

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

2. 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/ex...001034632.aspx

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•