SUMPRODUCT, IF, almost got it (I think) but it's just inches away...

coldsteel404

New Member
Joined
Nov 12, 2014
Messages
16
I use excel 2007
Hi,

I'm trying to do the following. I have 3 colums, one with the year, one with a quantity and one with prices

What I want to do is to calculate the average price of the year 2012; 2013, 2014 whilst taking the quantity into acount.

201292 € 156,00
201237 € 250,00
201212 € 500,00

<tbody>
</tbody>
201398 € 156,00
201330 € 250,00
201311 € 1.000,00

<tbody>
</tbody>

I can count the average price without taking the quantity into acount with =AVERAGE.IF($B$2:$B$13;G14;E2:$E$13)
I can also use SUMPRODUCT to calculate the matrixes for the total of money spend =SOMPRODUCT(D2:D13;E2:E13)
But I can't seem to combine them, let allone use the data as a criteria for if.

So, I hope I made myself somewhat clear (English isn't my native language so explaining Excelfunctions is rather challeging :)

I think I want something like this:
=AVERAGE.IF($B$2:$B$13;F14;SUMPRODUCT($C$2:$C$13;$D$2:$D$13))but without the message "error" :P

Thanks in adance,
 
Last edited:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Try this with your data in A1:C6

=SUMPRODUCT(--($A$1:$A$6=2012),$B$1:$B$6*$C$1:$C$6)/SUMIF($A$1:$A$6,2012,$B$1:$B$6)
 
Upvote 0
Hi, thanks for the effort but no, I'm afraid that didn't work. Perhaps it'd be a good idea to post the table I'm working with :)
 
Upvote 0
I can't add an Excelfile, is this option not supported or am I looking over it?
Anyway, this is the full table, without the used codes
TypeYearQuantityPrice
Type A201292 € 156,00
Type B201237 € 250,00
Type C201212 € 500,00Analyse
Type D20121 € 1.000,00
Type U20121 € 2.500,00
Type A201398 € 156,00Totale € 102.302,00
Type B201330 € 250,00
Type C201311 € 1.000,00
Type A2014102 € 156,00
Type B201434 € 250,00
Type C201416 € 500,00
Type D20143 € 1.000,00
201220132014
Different types534
Average price type € 881,20 € 468,67 € 476,50
Average price type (req. quantity)???
Total types per year143139155
Total money earned per year???

<tbody>
</tbody>

And here are the formulas I used thus far
=SUMPRODUCT(C2:C13;D2:D13)
=COUNT.IF($B$2:$B$13;F14)
=AVERAGE.IF($B$2:$B$13;F14;D2:$D$13)
=SOM.IF($B$2:$B$13;F14;$C$2:$C$13)
 
Last edited:
Upvote 0
It results in #name with me :/ (after I adjusted the ,into; (Belgium)
and the A's into B's
So yes, what I want is the average product per year of C and D.
 
Upvote 0
You will have to change the ranges to suit and also the commas to semicolons and perhaps even the formula names to suit your locale.
 
Upvote 0
=SOMPRODUCT(--($B$2:$B$13=F14);$C$2:$C$13*$D$2:$D$13)/SOM.ALS($B$2:$B$13;F14;$C$2:$C$13) thanks, your formula was fine,I was just being stupid and added the titles into your formula. No wonder it didn't work with:
=SOMPRODUCT(--($B$1:$B$13=F14);$C$1:$C$13*$D$1:$D$13)/SOM.ALS($B$1:$B$13;F14;$C$1:$C$13)

Works like a charm now, thank you very much :)
 
Upvote 0
Now just out if curiousity so I can do this myself in the future, what are the =SOMPRODUCT(--
and the /SOM.ALS for?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

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