Help with a "SUMIF" formula

[magicmark]

Hi All,

Need some help here if you can. I'll try and explain myself as best as I can ( I work in a small construction company and am trying to build an excel tool to show our spending etc per project. It's by no means a accounting reporting programme, just a tool to allow our engineers know our spend on certain projects or on certain items etc)

In each field in column "G" in worksheet entitled "Invoice - Expenses" I have drop down menu. The menu is a list of each project or job we are working on.
In each field in column "H" in the same worksheet ("Invoice - Expenses") I have another drop down menu. This menu is a list of "cost codes" i.e. all conceivable costs (fuel, material, labour, plant hire etc etc).
In column H is the actual relevant cost (ex tax).
Effectively I use this work sheet to record all our expenses. The drop down menus allow me allocate the cost to a particular job and cost code.

This allows me to filter the job and then filter each cost code showing the value of a particular cost per job / project.

I would prefer to eliminate the need to use the filters and use something more user friendly in a new "Executive Summary" sheet.

What I would like to do in field (for example) E4 have a drop menu listing all the jobs. In F4 have a drop menu listing all the cost codes. And then in G5, where the formula will live, the cost will magically appear i.e. if in E4 you choose "Newtown Bypass" and in F4 you choose "Labour" the figure in G5 will show the cost of Labour on the Newtown Bypass.

I hope all of the above makes sense, if you need any clarification please do not hesitate in asking.

Many thanks in advance for your help (unfortunately as you can probably tell I'm not that good with Excel so I prob won't be able to return the favou ha)

Cheers

Mark

[djapigo]

Hi Mark,

It will help us if you attach a sample spreadsheet so that we can see what you need. Also, enter the results that you want to see in different scenarios.

To attach a sample spreadsheet, click on the Go Advanced button and look for a paperclip in the icons or Manage Attachments near the bottom.

Thanks,
Dennis

[magicmark]

Hi thanks for the advice,

I have attached the template for this file. Hope this helps.

Mark

[djapigo]

Hi Mark,

Where are your costs? Are they in Profit and Loss?

If so, you should use this formula in 'Executive Summary'!G4 = VLOOKUP(E4,'Profit and Loss'!$E$4:$F$17,2,0)+VLOOKUP(F4,'Profit and Loss'!$G$4:$H$39,2,0)

Of course, change the operation where needed... the VLOOKUP (or INDEX/MATCH) is a great way to look up values from a range.

See if you can work with this.

Let us know if you have more questions.

Dennis

[magicmark]

Hi Dennis,

Thanks very much for your help. I have entered a fictional invoice / purchase in the template which I have re-attached.

All expenses (apart from wages) are logged in worksheet "Invoices - Expenses". You will note I logged a fictional receipt for concrete for $1000. You will note I assigned this cost to job "Bega Drainage" (column G) and to cost code "Direct Material" (column H) and as mentioned the cost / value before tax is $1000 (column I).

Now if we move to worksheet "Executive Overview" and in particular the drop down menus in E4 and F4. If I choose "Bega Drainage" in E4 and "Direct Material" in F4 then $1000 should appear in F5.

Does this make sense?

Thanks again Dennis

Mark

[djapigo]

Will this work for F5 (or G4)?

=SUMIFS('Invoices - Expenses'!$I$3:$I$1000,'Invoices - Expenses'!$G$3:$G$1000,E4,'Invoices - Expenses'!$H$3:$H$1000,F4)

[magicmark]

Hi Dennis I have tried your formula but I just get a "0" in the G5 field. I tried to fiddle about with your formula but don't know enough to fix it.

[djapigo]

Sorry Mark,

I gave you a formula that will only work in Excel 2007+

Try this instead...
=SUMPRODUCT(('Invoices - Expenses'!$I$3:$I$1000)*('Invoices - Expenses'!$G$3:$G$1000=E4)*('Invoices - Expenses'!$H$3:$H$1000=F4))