Sumproduct based on another column

ANTONIO1981

Board Regular
Joined
Apr 21, 2014
Messages
162
HI all


I'm looking for a formulae to calculate the total costs in "tab numbers"

it needs to sum the costs of all sites (for example water) in a month:

the volume in each site (tab volumes_table) multiplied by the cost each site
(tab variable cost_table)

example of one site only

(AT1 Volume in january= 4702.6)*(cost of water in AT1 In january=0.167) =785.33

the link will help to understand


question mr excel 2.xlsx - Google Drive

thanks in advance

Anthony
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
1) Your actual valyue for Austria AT1 in Jan-18 is 4702.58001188331
All your values are like this.

2) There's no 2017 data in the Volumes and Variable Cost table.
Should the Numbers summary have dates of 2018 instead?

3) There are no refernces to AT1 in the Numbers table either.
 
Last edited:
Upvote 0
1) Your actual valyue for Austria AT1 in Jan-18 is 4702.58001188331
All your values are like this. ( it is fine is irrelevant, in practice will have no decimals)

2) There's no 2017 data in the Volumes and Variable Cost table.
Should the Numbers summary have dates of 2018 instead? ( yes indeed sorry i left accidentally 2017 values)

i updated the link

https://drive.google.com/open?id=0B6D0Iz0qoI6HaS01bFRTd28zN2c

thanks in advance
 
Upvote 0
3) There are no refernces to AT1 in the Numbers table either.

Still no AT1 references so how does the table relate to AT1, at the moment it doesn't, it relates to all water in Jan-18.

the value in Numbers C2 is currently 5.01 (displaying as 5)
=SUMPRODUCT((VARIABLE_COST_TABLE!B$2:B$2000=C$1)*(VARIABLE_COST_TABLE!C$2:C$2000=$B2)*(VARIABLE_COST_TABLE!D$2:D$2000))
 
Last edited:
Upvote 0
(my answer)

3) There are no refernces to AT1 in the Numbers table either.


Still no AT1 references so how does the table relate to AT1, at the moment it doesn't, it relates to all water in Jan-18.

(exactly, what i need is all water in Jan-18, all sites included)

( i showed the example of one site AT1 just to show who is calculated one site, but what i need to know is the total cost (all sites included) in each type of cost per month.

(total cost of water (all sites included) in january, febuary...
total cost of electricity (all sites included) in january, febuary...
etc...

i hope is clear

thanks in advance

Anthony)
 
Last edited:
Upvote 0
the formula doesn't work as it is ignoring the fact that every site has different volumes and and costs. (that's the whole point of my question)

(in this particular case the costs are the same but is not always the case)

the cost of water in January is [TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]15,858 [/TD]
[/TR]
</tbody>[/TABLE]
not 5...



site volumes costs total
AT1 4,703 0.167 785
AT17 3,617 0.167 604
AT19 3,617 0.167 604
AT2 4,920 0.167 822
AT25 3,523 0.167 588
AT26 4,341 0.167 725
AT28 2,930 0.167 489
AT3 2,307 0.167 385
AT30 2,568 0.167 429
AT31 2,994 0.167 500
AT32 4,486 0.167 749
AT33 5,064 0.167 846
AT34 3,256 0.167 544
AT35 3,542 0.167 592
AT36 4,002 0.167 668
AT38 1,700 0.167 284
AT39 3,509 0.167 586
AT40 4,160 0.167 695
AT41 3,634 0.167 607
AT42 3,418 0.167 571
AT43 3,219 0.167 538
AT44 4,268 0.167 713
AT45 4,285 0.167 716
AT47 4,160 0.167 695
AT48 4,341 0.167 725
AT49 - 0.167 -
AT50 - 0.167 -
AT51 - 0.167 -
AT52 - 0.167 -
AT7 2,391 0.167 399

thanks in advance

Anthony
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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