Excel 2010 - Calculating totals but only on specific rows - VLOOKUP?

[Brickinnit]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Unit Name
[/TD]
[TD]June 2018 Instances
[/TD]
[TD]July 2018 Instances
[/TD]
[TD]August 2018 Instances
[/TD]
[TD]Cost (pounds)
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]dgfg
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]25
[/TD]
[/TR]
[TR]
[TD]fgdfg
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]sfsdf
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]35
[/TD]
[/TR]
[TR]
[TD]sdfsdgdfgfd
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]40
[/TD]
[/TR]
[TR]
[TD]sdfsdf
[/TD]
[TD][/TD]
[TD][/TD]
[TD]6
[/TD]
[TD]45
[/TD]
[/TR]
[TR]
[TD]sfsdf
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]sdfsdfsfdsdf
[/TD]
[TD][/TD]
[TD]5
[/TD]
[TD][/TD]
[TD]55
[/TD]
[/TR]
[TR]
[TD]dsfsdf
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]60
[/TD]
[/TR]
</tbody>[/TABLE]

Hi Excel Community,

This is the first time I have posted so apologies if any mistakes I did read the posting guide and already searched for a solution but no luck so far - hope you can help

Anyway, please see posted table above. I would like to quickly calculate the totals for each month (June, July then August). June total cost would be ((1X25)+(2X35)+(3X50)) = 245 pounds. July would be ((2X30)+(3X40)+(5X55)) = 455 pounds etc. I can obviously do with a simple sum of each total but if have lots of rows, this becomes rather long-winded (for example (=SUM(B3*E3)+(B4*E4) etc etc. Is there an easier way via, for example, VLOOKUP? I tried using combination of VLOOKUP, SUM, PRODUCT etc but couldn't find a way to work.

Hope this makes sense and thanks so much in advance for the help.

Brick

 

[arthurbr]

For June
=SUMPRODUCT(($E$3:$E$10)*(INDEX($A$3:$E$10,,MATCH(C$1,$A$1:$E$1,0))))
Pull right for other values and adapt ranges as needed

 

[oldbrewer]

[TABLE="width: 658"]
<colgroup><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Unit Name[/TD]
[TD]June 2018 Instances[/TD]
[TD]July 2018 Instances[/TD]
[TD]August 2018 Instances[/TD]
[TD]Cost (pounds)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name3[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name4[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]45[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name6[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name7[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]55[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/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][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]

<tbody>
</tbody>

[/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][/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][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]June 2018 Instances[/TD]
[TD]July 2018 Instances[/TD]
[TD]August 2018 Instances[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]245[/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][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]245 got by[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]=SUMPRODUCT((B3:B10)*(E3:E10))[/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][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Johnny C]

There's 2 options.

1) Shift+F9 - this will calculate the current sheet only.
However I've found it sometimes takes longer to calculate the sheet than the whole workbook. That's because (I think!) Excel uses what it calls a calculation tree - a ginormous 'tree' in memory of every calculation in the workbook. To calculate the sheet it has to rebuild that calculation tree to isolate the sheet calculations. The only way to speed it up is to do a 'full rebuild' of the tree (ctrl+Shift+Alt+F9) and then do a series of sheet calculations.

2) VBA - you can build a small macro to only calculate the range you have selected.
https://www.mrexcel.com/forum/excel-questions/11242-calculate-only-specified-range.html

If VBA scares you don't let it. There's a few things to do before you gets started - enable the Develop tab on the ribbon (right click ribbon>Customise the ribbon and check the Developer box on the right. Then record a macro - bottom left of the screen to the right of where it says 'READY' there's an icon of a calculator, click, that opens a dialog box. Change Store macro in to 'personal macro workbook' and hit OK. Then just type something and press return, then press the icon next to ready which will be a white block. Then hit alt-F11 to open the macro editor. Find Personal.xlsb on the left, look in modules and double click Module1.
You should see the macro you created. then copy and paste this :-

sub calcnow()
Selection.calculate
end sub

Click the save button at the top and close the editor down, then click on the Developer tab, and at top left double click macros. Where you see PERSONAL.XLSB!Calcnow select it, click options and change the shortcut key to a capital E.

Now whenever you have a range selected, press ctrl+E and it will only calculate the cells you have selected.

 

[Brickinnit]

Thanks for all the replies - what a helpful community