Excel Sum product values and stock then multiplie when multiple criteria

Question

So I have this information:

+---------------+---------+-------+------------+
|   Chocolate   |  Brand  | Stock | Sale value |
+---------------+---------+-------+------------+
| Chokito       | Nestlé  |  1520 | $3,50      |
| Snickers      | Mars    |  3300 | $5,20      |
| Snickers 2    | Mars    |   500 | $2,50      |
| Kit Kat       | Nestlé  |  2000 | $9,10      |
| Double Decker | Cadbury |  1000 | $2,50      |
| Idaho         | Mars    |     0 | $6,10      |
| Caramello     | Cadbury |   350 | $7,50      |
| Cadbury Daily | Cadbury |  1000 | $3,10      |
| Almond Joy    | Hershey |   500 | $1,50      |
| Twix          | Nestlé  |   999 | $4,50      |
| Zero Bar      | Hershey |   488 | $5,50      |
+---------------+---------+-------+------------+

Wha I want to get the total stock value for each brand. I get these values by inserting a column of of stock * value then doing a Pivot Table

Cadbury  $8.225,00 
Hershey  $3.434,00 
Mars     $18.410,00 
Nestlé   $28.015,50

But what I want to do is a formula in Excel that will get this same values. I first tried using SUMIF but obvioulsy it didnt worked xD

I cant think of any other formula Thanks for your help

This is a homework assignment isn't it? Did you at least *try* to solve it yourself? — ashleedawg, Mar 30 '18 at 19:04

score 0 · Answer 1 · 2018-03-30T19:18:56.247

0

Try,

=SUMPRODUCT((C$2:C$12), (D$2:D$12), --(B$2:B$12=G4))

For a dynamic length of data,

=SUMPRODUCT((C$2:INDEX(C:C, MATCH(1E+99, C:C))), (D$2:INDEX(D:D, MATCH(1E+99, C:C))), --(B$2:INDEX(B:B, MATCH(1E+99, C:C))=G4))

edited Mar 30 '18 at 19:18

answered Mar 30 '18 at 18:56

@ Jeeped - you surprise me sometimes! Bored? – ashleedawg Mar 30 '18 at 19:05
I guess I shouldn't talk... I just spent a *wee bit* too much time on one of the examples for [my answer](https://stackoverflow.com/a/49577009), that probably nobody would even notice if I didn't point it out... – ashleedawg Mar 30 '18 at 19:09
Thanks. After posting I found this same solution, but I am getting a problem: I want to refer to the whole column like C:C; D:D and B:B cause I may get more values, but because of text headers I got an error. Do you know how to solve it? – Junior Santos Mar 30 '18 at 19:13
@JuniorSantos must not be the *same* solution then. :) – ashleedawg Mar 30 '18 at 19:14
@JuniorSantos you dont want to do full column references with sumproduct or any array calculations as you will wind up doing excessive calculations. When going full column reference, better off using SUMIF or SUMIFS. – Forward Ed Mar 30 '18 at 19:29
@ jeeped Does setting the end of the index within the SUMPRODUCT save on the excess calculations of using full referenced columns? – Forward Ed Mar 31 '18 at 13:27

score 0 · Answer 2 · answered Mar 31 '18 at 13:26

Alternative approach using sumif

Place the following in E2 and copy down

=D2*E2

this will give the value you of each individual chocolate level in stock

in column G generate a list of brands

in H2 use the following formula and copy down as needed

=SUMIF(B:B,G2,E:E)

Excel Sum product values and stock then multiplie when multiple criteria

2 Answers2