Results 1 to 5 of 5

Formula to asses a range of cells and return a calculation if found depending on its value

Threaded View

  1. #1
    Registered User
    Join Date
    01-10-2013
    Location
    Kent, England
    MS-Off Ver
    Excel Mac 2011
    Posts
    16

    Formula to asses a range of cells and return a calculation if found depending on its value

    Hello,

    I have attached my excel spreadsheet.

    Sheet 1 is a data entry sheet named "Job Cost Sheet"
    Sheet 2 is a summary sheet named "Job Summary Sheet"
    Sheet 3 is a list sheet named "list" designed for data validation drop down menu on sheet 1.

    I am trying to get a formula of some description that will accomplish the following:

    The spreadsheet is designed to run a job cost for each of the jobs I run for my construction company.

    On sheet 1 I have line by line items that each have a cost in the cost column (E4:E20), In the column (F4:F20) The user will have to enter an E,O,L or M (Standing for Extra, Overhead, Labour & Material)

    On Sheet 2 (A8:A11) Is the same headings (A8=Labour, A9=Materials, A10=Overheads, A11=Extra)
    (B4:B11) is where I would like the data to be automatically imported to.

    Here's my issue (Finally)
    I would like a formula that checks the data on sheet 1 column F then depending on what letter is in each cell i'd like the amount in the cost column to be added up and sent to the relevant cell (B4:B11)

    e.g.

    line 1 (Sheet 1 - F4 = "M") E4 = £3600.00
    line 2 (Sheet 1 - F5 = "M") E5 = £1000.00
    line 3 (Sheet 1 - F6 = "L") E6 = £1000.00
    line 4 (Sheet 1 - F7 = "O") E7 = £150.00

    Therefore the Sheet 2 should show the following

    A8 = "Labour" (B8 SHOULD EQUAL £1000.00)
    A9 = "MATERIALS"(B9 SHOULD EQUAL £4600 = £3600.00+£1000.00)
    A10 = "OVERHEADS"(B10 should equal £150)
    A11 = "EXTRA" (B11 SHOULD STAY BLANK AS THERE ARE NO "E's" IN THE COLUMN)

    I hope this makes sense and someone can help me. Please see sheet attached.

    Thanks in advance.

    Kind regards
    Stewart
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1