Results 1 to 2 of 2

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

  1. #1

    Question Formula Help. Sum of Cells if Certain Criteria is Met



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    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)

    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
  •