Look up and return data from a Description

[pmw19800]

hello All

Please see attahed

Demand Tab
Data is pasted into this tab; Colum A to N
Variants can constantly change as the customer demand changes. SO there can be 100’s of part numbers can appears within this list and often are in no order, i have grouped them manueley

I’d like a formula look at the data and report on the total demand from the Description ONLY for example look for each line with ESH and return column and sum that total in S16
Expected outcome is highlighted in Blue

[JohnTopley]

in S16

=SUMPRODUCT((LEFT($C$3:$C$500,3)="ESH")*($C$3:$C$500<>""),($N$3:$N$500))

[pmw19800]

Thanks John.

That works great, however I’ve over looked I have 3 types of caps.
Could an additional lookup be also entered, Example in yellow attached
In S13 look at column B see it’s a LHL(405) then look at Colum C and return Colum N as to you previous suggested Formula


Paul

[JohnTopley]

There is no automatic way of knowing the codes: for S16 how would Excl know to use "ESH" : column B has GRP and there is nothing in Q16 relating to either B or C.

[pmw19800]

your orginal formula works perfect for the itmes in green
=SUMPRODUCT((LEFT($C$3:$C$500,3)="ESH")*($C$3:$C$500<>""),($N$3:$N$500))


Using the items highlighted in yellow and orange as an examples,
Both share the Description SHA, but are in fact different, Colum B shows the difference, one is LHL(405) and the other is N/L(405)
Is there a way a formula could look up at N/L(405) Colum B then look at SHA Colum C and returns column N


Paul

[JohnTopley]

in S11

=SUMPRODUCT((LEFT($C$3:$C$500,3)=MID($Q13,9,3))*($C$3:$C$500<>"")*(LEFT($B$3:$B$500,3)=MID($Q13,5,3)),($N$3:$N$500))

copy down to S12

BUT will not work for S16 or (many) others i suspect.

[pmw19800]

John, Thats Brilliant works PERFRCT