Formula calculating a projected Charge

M

Muddypaws

New Member
Joined
Dec 3, 2009
Messages
28
Its a difficult one to explain but I have spent hours trying to work this out and I have literally hit a brick wall. Not sure how to upload the spreadsheet to make it easier to explain so apologies. I have inherited this and I just dont know how to work this out and I know what I want to work out but not sure how to do it.

First one is to look up all zone one data (tab2) and return number of planned jobs and restoration jobs for that zone (tab1). I have 9 zones in all. 17 I know is wrong as thats the total for all, what I need is the 17 split between Tyne & Wear and West Yorkshire Etc


Tab 1


[TABLE="width: 861"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]Live Generator Count[/TD]
[TD="colspan: 2"]Total Days Connected[/TD]
[TD="colspan: 2"]Total Running Costs[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Planned[/TD]
[TD]Restoration[/TD]
[TD]Planned[/TD]
[TD]Restoration[/TD]
[TD]Planned[/TD]
[TD]Restoration[/TD]
[/TR]
[TR]
[TD]Tyne & Wear[/TD]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]West Yorkshire[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]South Yorkshire[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Teesside[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Humber Estuary[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Northumberland + Co Durham[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yorkshire Moors + Wolds[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yorkshire Dales[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]North Lincolnshire[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Tab 2

[TABLE="width: 586"]
<tbody>[TR]
[TD]ZONE[/TD]
[TD]RESTORATION/PLANNED[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]02-TYNE AND WEAR[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]02-TYNE AND WEAR[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]02-TYNE AND WEAR[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]02-TYNE AND WEAR[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]02-TYNE AND WEAR[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]02-TYNE AND WEAR[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]02-TYNE AND WEAR[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]02-TYNE AND WEAR[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]02-TYNE AND WEAR[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]03-TEESSIDE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]03-TEESSIDE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]03-TEESSIDE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]03-TEESSIDE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]03-TEESSIDE[/TD]
[TD]RESTORATION[/TD]
[/TR]
</tbody>[/TABLE]





Second one is and this is totally driving me nuts. Tab 2 needs to look at tab 1. Look for all 100kva generators, and return how much it will cost for the number of days hire. So day 1 is say £677.85 and all days after that is £29.61. So if a 15kva generator has run 22 days then it will return £1299.66. Problem is there on different tabs.

Probably none of the above makes sense and I dont know how to upload the spreadsheet


Tab 1

[TABLE="width: 555"]
<tbody>[TR]
[TD]Generator Size[/TD]
[TD]1st Day[/TD]
[TD]Subs Days[/TD]
[TD]1 Week Hire[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]£677.85[/TD]
[TD]£29.61[/TD]
[TD]£855.51[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]£682.90[/TD]
[TD]£35.69[/TD]
[TD]£897.04[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]£682.90[/TD]
[TD]£35.69[/TD]
[TD]£897.04[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]£722.40[/TD]
[TD]£53.39[/TD]
[TD]£1,042.74[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]£722.40[/TD]
[TD]£53.39[/TD]
[TD]£1,042.74[/TD]
[/TR]
[TR]
[TD]150[/TD]
[TD]£1,025.53[/TD]
[TD]£90.57[/TD]
[TD]£1,568.95[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]£1,025.53[/TD]
[TD]£90.57[/TD]
[TD]£1,568.95[/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD]£1,535.61[/TD]
[TD]£152.08[/TD]
[TD]£2,448.09[/TD]
[/TR]
[TR]
[TD]320[/TD]
[TD]£1,535.61[/TD]
[TD]£152.08[/TD]
[TD]£2,448.09[/TD]
[/TR]
[TR]
[TD]500[/TD]
[TD]£2,134.46[/TD]
[TD]£243.95[/TD]
[TD]£3,598.16[/TD]
[/TR]
[TR]
[TD]750[/TD]
[TD]£2,918.10[/TD]
[TD]£367.19[/TD]
[TD]£5,121.24[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]£2,918.10[/TD]
[TD]£367.19[/TD]
[TD]£5,121.24[/TD]
[/TR]
[TR]
[TD]1250[/TD]
[TD]£3,499.55[/TD]
[TD]£501.49[/TD]
[TD]£6,508.49[/TD]
[/TR]
</tbody>[/TABLE]



Tab 2

[TABLE="width: 1269"]
<tbody>[TR]
[TD]ZONE[/TD]
[TD]RESTORATION/PLANNED[/TD]
[TD]GENERATOR SIZE (KVA)[/TD]
[TD]DATE/TIME CONNECTED TO THE NETWORK[/TD]
[TD]DAYS CONNECTED[/TD]
[TD]PROVISIONAL COSTS[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[TD]100[/TD]
[TD]29/06/2018 00:00[/TD]
[TD]22[/TD]
[TD]£1,299.66[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Answer to #1 ...

First change all of the names in tab 1 to match tab 2. For example, Change "Tyne & Wear" to "02-TYNE & WEAR". Or you can change them all to "Tyne & Wear", bottom line is they have to match in order for this formula to work.

Second, I am assuming "17" in your example is in B3 and the columns in tab 2 are A and B. Also, I am assuming your sheet names are "Sheet1" & "Sheet2" If not, the formula will have to be modified.

Finally, insert the first formula into B3 and drag it down auto fill to the bottom. Paste the second formula into B4 and drag it down to the bottom.
Code: 
=COUNTIFS(Sheet2!A:A,Sheet1!A3,Sheet2!B:B,Sheet1!B2)

Code: 
=COUNTIFS(Sheet2!A:A,Sheet1!A3,Sheet2!B:B,Sheet1!C2)

Hope this helps.
 
Upvote 0
Captain_Conman said:
Answer to #1 ...

First change all of the names in tab 1 to match tab 2. For example, Change "Tyne & Wear" to "02-TYNE & WEAR". Or you can change them all to "Tyne & Wear", bottom line is they have to match in order for this formula to work.

Second, I am assuming "17" in your example is in B3 and the columns in tab 2 are A and B. Also, I am assuming your sheet names are "Sheet1" & "Sheet2" If not, the formula will have to be modified.

Finally, insert the first formula into B3 and drag it down auto fill to the bottom. Paste the second formula into B4 and drag it down to the bottom.
Code: 
=COUNTIFS(Sheet2!A:A,Sheet1!A3,Sheet2!B:B,Sheet1!B2)

Code: 
=COUNTIFS(Sheet2!A:A,Sheet1!A3,Sheet2!B:B,Sheet1!C2)

Hope this helps.
Click to expand...


I have tried it and it does not work, its a bit of a hatchet job which is why its confusing me and no one knows how to do it. hope this helps so in effect Tyne and Wear should have 5 restorations and Teeside should have 3 restorations and West Yorkshire 11 planned.

Sheet 1 (is my summary)

[TABLE="width: 984"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]Live Generator Count[/TD]
[TD="colspan: 2"]Total Days Connected [/TD]
[TD="colspan: 2"]Total Running Costs [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Planned [/TD]
[TD]Restoration [/TD]
[TD]Planned [/TD]
[TD]Restoration [/TD]
[TD]Planned [/TD]
[TD]Restoration [/TD]
[/TR]
[TR]
[TD]02-TYNE AND WEAR[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08-SOUTH YORKSHIRE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03-TEESSIDE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05-YORKSHIRE MOORS & WOLDS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04-YORKSHIRE DALES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]


[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2 (is my data)

[TABLE="width: 438"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]ZONE[/TD]
[TD]FAULT/PLANNED WORK[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]02-TYNE AND WEAR[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]03-TEESSIDE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]04-YORKSHIRE DALES[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]02-TYNE AND WEAR[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]02-TYNE AND WEAR[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]03-TEESSIDE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]04-YORKSHIRE DALES[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]02-TYNE AND WEAR[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]02-TYNE AND WEAR[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]03-TEESSIDE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]05-YORKSHIRE MOORS & WOLDS[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Try this one in B3...

=COUNTIFS(Sheet2!A:A,"02-TYNE AND WEAR",Sheet2!B:B,"Planned")

This should work for your summary page, but you will have to change the zone and planned/restoration for each cell you put it in.
 
Upvote 0
Muddypaws said:
I have tried it and it does not work, its a bit of a hatchet job which is why its confusing me and no one knows how to do it. hope this helps so in effect Tyne and Wear should have 5 restorations and Teeside should have 3 restorations and West Yorkshire 11 planned.

Sheet 1 (is my summary)

[TABLE="width: 984"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]Live Generator Count[/TD]
[TD="colspan: 2"]Total Days Connected[/TD]
[TD="colspan: 2"]Total Running Costs[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Planned[/TD]
[TD]Restoration[/TD]
[TD]Planned[/TD]
[TD]Restoration[/TD]
[TD]Planned[/TD]
[TD]Restoration[/TD]
[/TR]
[TR]
[TD]02-TYNE AND WEAR[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08-SOUTH YORKSHIRE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03-TEESSIDE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05-YORKSHIRE MOORS & WOLDS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04-YORKSHIRE DALES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2 (is my data)

[TABLE="width: 438"]
<tbody>[TR]
[TD]ZONE[/TD]
[TD]FAULT/PLANNED WORK[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]02-TYNE AND WEAR[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]03-TEESSIDE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]04-YORKSHIRE DALES[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]02-TYNE AND WEAR[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]02-TYNE AND WEAR[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]03-TEESSIDE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]04-YORKSHIRE DALES[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]01-NORTHUMBERLAND & COUNTY DURHAM[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]02-TYNE AND WEAR[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]02-TYNE AND WEAR[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]03-TEESSIDE[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]05-YORKSHIRE MOORS & WOLDS[/TD]
[TD]RESTORATION[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
[TR]
[TD]06-WEST YORKSHIRE[/TD]
[TD]PLANNED[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

i WOULD LIKE TO SAY IN BIG CAPITALS A MASSIVE THANK YOU FOR THIS, IT TOOK A WHILE BUT I GOT THERE IN THE END, EXCEL IS A VERY DIFFICULT APPLICATION FOR ME TO USE, BUT ONCE I HAVE A BIT OF GUIDANCE I SORT OF CAN GET ON WITH IT. A HUGE THANK YOU AND A VERY HAPPY BUNNY THIS END
 
Upvote 0
If I, which I do need to add up a column in the same way rather than the numbe rof instances do i just use "SUMIF"
 
Upvote 0
Yes! However, if you have multiple criteria, you may have to use SUMIFS.

For example, we had to use COUNTIFS in the first problem because you were looking at the zone and planned/restoration. If you have multiple criteria again, make sure it is SUMIFS.
 
Upvote 0
For Question #2

Try inserting the following formula into your "Provisional Cost" column on Sheet 2

=VLOOKUP($C2,Sheet1!$A:$B,2,FALSE)+VLOOKUP(Sheet2!$C2,Sheet1!$A:$C,3,FALSE)*(Sheet2!$E2-1)

Again I am making assumptions on which columns your data lies in, so if you have issues let me know.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top