Calling all Excel gurus!
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 |
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 |
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 |
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 |
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 |
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 |
thanks, will give it a whirl
|
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 |
All times are GMT. The time now is 23:42. |
Copyright © 2024 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.