Sumproduct based on another column

[ANTONIO1981]

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

 

[Special-K99]

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.

 

[ANTONIO1981]

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

 

[Special-K99]

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))

 

[ANTONIO1981]

(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)

 

[Special-K99]

Just copy the above formula across and down the columns to fill out that table.

 

[ANTONIO1981]

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

15,858

<tbody>
</tbody>

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