Calling all Excel gurus!

[EESDL]

I run a spreadsheet, part of which I put flying hours, all billed at different rates.
So for one day you'd have maybe 5 different 'rate' columns with the total flying for the day at the end of the row.
The flight can relate to 8 different jobs, ie charter, personal, business1, business2 etc.
So first column of spreadsheet is a column where I identify which business to invoice with numbers 1- 8.
I need to show how many hours I've done for each category 1- 8.

What I need to work out is how I can sum all the hours on the right, that have a particular reference on the left to give me 8 totals, each may have a mix of the 5 different charge rates.

Codes on left will not be in order so VLookup doesn't work!

Code Date Rate1 Rate2 Rate3 etc
3 0.8 0.4 0.5
1 1.7 1.8 1.1
1 0.2
4 1.7 1.6



Thank you in advance

[Genghis the Engineer]

Somewhere off to the right where you aren't looking use eight columns.

In each, use terms along the lines of IF(F3=1,D3,0) [If F3=1, value from D3, else 0]

Then put a SUM term at the top of each column.

Then somewhere more convenient just X-ref to that sum cell.

G

[ExGrunt]

EESDL

I think this has a fairly straight forward solution. But, I am not clear on how you want to present the results.

As I read your outline:

Each row has one job with a 'job code'. That job has a total number of hours split between five 'cost codes'.

Each date can contain a number of job rows, which is indicated by the date column in the job row.

The row entries are in random order.

What I need to know is how you what to show the output?

Do you want a table? ie:

Columns with cost codes.
Rows with job codes.

summarised data in the table.

Or do you need something else?

EG

[EESDL]

Thanks
Genghis, I was on the verge of doing the same when I thought that there must be an easier solution amongst all the 'functions'

Ex Grunt
You got it in one.
Require the result as a simple list (on my FY annual summary page) with a running total ie

Charter £total amount of revenue allocated code 1
Business 1 £blah amount of revenue allocated code 2
Business 2 £blah etc

Then I can provide adequate briefs/pie-charts etc

Thank you in advance

[Genghis the Engineer]

There are functions which will do this sort of thing, and I've used them on occasion.

But, the advantage (in my opinion) of doing it the way that I suggested is that the data analysis is more visible so that if there are any errors in your spreadsheet it's much easier to track down and correct.

G

[ExGrunt]

Solution - sorry for the delay

Hello EESDL,

I have been away and could not respond sooner, I suggest:

Assuming:

Cells B2:H2 contain titles: Code, Date, Rate1, Rate2, Rate3, Rate4, Rate5.

Cells B3:B25 contain jobcodes
Cells C3:C25 contain Dates
Cells D3:D25 contain Hours Rate1
Cells E3:E25 contain Hours Rate2
Cells F3:F25 contain Hours Rate3
Cells G3:G25 contain Hours Rate4
Cells H3:H25 contain Hours Rate5

In Cells K2:P2 enter: Code, Rate1, Rate2, Rate3, Rate4, Rate5.
In Cells K3:K10 enter the codes: Charter, Personal, Business1, Business2, Business3, Business4, Business5, Business6.

Then in Cells L3:P10 enter the formula =SUMPRODUCT(($B$3:$B$25=$K3)*($D$2:$H$2=L$2)*$D$3:$H$25)

Obviously when you go past row 25 you have to extend the formula ranges: $B$3:$B$25 and $D$3:$D$25.

I would echo Genghis' point that for a business application there needs to be some form of error checking calculation.

Hope this helps

EG

[EESDL]

thanks, will give it a whirl

[ExGrunt]

Clarification

On reflection I thought some further clarification is needed:

The calculation is based on decimal time - ie 1 hour 6 minutes is entered as 1.1

When I say:

Then in Cells L3:P10 enter the formula =SUMPRODUCT(($B$3:$B$25=$K3)*($D$2:$H$2=L$2)*$D$3:
$H$25)

I actually mean:

Then in Cell L3 enter the formula =SUMPRODUCT(($B$3:$B$25=$K3)*($D$2:$H$2=L$2)*$D$3:
$H$25)

And then copy it into Cells L3:P10

EG