Dear all


Im preparing a material orden template at work.
Materials can go either on floors or walls.
Next to each option (floor or wall) I've created a drop down list with all available colours.
So you could have wall 1 in colour 1, wall 2 in colour 2, wall 3 in colour 3, and floor 1 in colour 2, floor 2 in colour 1 and floor 3 in colour 3

What i would like to do is, matching the quantities of each colour. In this case, i would need to sum up for colour 1 walls 1 and floor 2, for colour 2 wall 2 and floor 2 and for colour 3 wall 3 and floor 3.

I tried doing VLOOKUP but it's not what i need.
I need a function that can recognise the parameters in the drop down list, bulk them together and bring up the sum of quantities signed to each colour.

If you see the xls file, for example for the colour CEMENTO, I have 1 meter, 2 meters and 9 meters.
I need a formula that can identify the colour, and bring back the sum of 1 +2 +9

THANKS

Attached Files

Material order TOPCRET-2.xlsx (39.9 KB, 10 views)

Look in cells G7:H15 in the Copia de Material order sheet in the attached.
Formulae like:
=SUMIF($E$8:$E$28,G7,$C$8:$C$28)

Attached Files

msoforums38364Material order TOPCRET-2.xlsx (40.4 KB, 13 views)

i think you got it
thanks a lot!

Dear P45cal,
Can we have it so that instead of looking for a specific value, in this case G7, it looks within a range of values?
I have 36 colours from which to look for, and if I use the formula as you specified it, I would need 36 rows, one for each colour, which would be messy.
I would need the formula to first group all the colours that appear above (as in column G), and then do the sum.
Can you give me a hand with that?
Thanks a lot!!!

You can do this easily with a pivot table. See attached.
You will have to right-click the table and choose Refresh if you change the source data.

Attached Files

msoforums38364Material order TOPCRET-3.xlsx (44.0 KB, 10 views)