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

[shoo0204]

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

[vlady]

try

Please Login or Register  to view this content.

or look on
sumproduct function

[Moo the Dog]

Try this:

=SUMIF('Job Cost Sheet'!$F$4:$F$26,"L",'Job Cost Sheet'!$E$4:$E$26)

Change the "L", to the other letters for the other rows (M, E, and O)

- Moo

[shoo0204]

Try this:

=SUMIF('Job Cost Sheet'!$F$4:$F$26,"L",'Job Cost Sheet'!$E$4:$E$26)

Change the "L", to the other letters for the other rows (M, E, and O)

- Moo

Thanks Moo that worked perfectly.

Thanks again
Stewart

[Moo the Dog]

Glad to help.

- Moo