How to make SUM based on lookup table

**eeman82** · 12-21-2012, 10:30 AM

I want to SUM project A & B based on productcodes.

So Project A has to be (11+14) = 25 euros.

What formula should I use?

SUMIF and SUMPRODUCT didnt bring me the answers yet...

Thanks in advance!

-------------------

Product Amount Project Product
L15 € 10,00 A L25
L25 € 11,00 A L41
L31 € 12,00 B L61
L33 € 13,00 B L81
L41 € 14,00
L43 € 15,00
L51 € 16,00
L53 € 17,00
L61 € 18,00
L81 € 19,00

Project Amount
A € 25,00
B € 37,00

**kbkumar** · 12-21-2012, 10:44 AM

Hi - Welcome to the forum.

To keep this simple, I would use column C as a 'helper column' and use the below formula in cell C2 and below:

Formula:

=IFERROR(INDEX($D$2:$D$11,MATCH(A2,$E$2:$E$11,FALSE),),"")

Then in cell E14 use:

Formula:

=SUMIF($C$2:$C$11,"A",$B$2:$B$11)

and in cell E15 use:

Formula:

=SUMIF($C$2:$C$11,"B",$B$2:$B$11)

Hope this helps.

**Fotis1991** · 12-21-2012, 10:45 AM

In F2 and copy down put this formula. This will be a helper and hidden if you like column)

=INDEX($B$2:$B$11,MATCH(E2,$A$2:$A$11,0))

Then in E14 and copy down, put this one.

=SUMIF($D$2:$D$5,D14,$F$2:$F$5)

**eeman82** · 12-21-2012, 02:07 PM

Great!

The solution from Fotis1991 worked for me, it makes it able to also SUM similar projectcodes on one project.
Thanks for helping me out!

The solution always looks simple aft