Apr 10 2022 08:57 PM - edited Apr 10 2022 09:13 PM
Hello,
I have a pricing spreadsheet from our supplier, which gives their part numbers in column A. But many parts are available in different colours, so they have used a "Colour Code" entry in column C. (This is a numerical code that matches to the different colours eg 10=white.) Typically each colour is a different price for each part. They then have duplicate entries for each part number, with a different colour code for each row.
I'm creating a separate spreadsheet for only the parts we stock. I need to be able to match the supplier's part number in column A then also match the colour code. In the simplified example attached we only stock black jeans (colour code 11), so I need the price for Part Number 1308950 colour code 11, but not the other colours.
I hope that makes sense. Any input would be appreciated!
Lisa
Apologies that I haven't attached the sample as a file, I can't find the attachment button
Apr 10 2022 10:25 PM
@LisaPriest In J3, try this:
=SUMIFS($D$3:$D$100,$A$3:$A$100,H3,$C$3:$C$100,I3)
Change the sum range (in column D) and the criteria ranges (columns A and C) to ones that correspond with your real schedule. Have attached a mock-up based on the screenshot.
Apr 11 2022 01:48 AM
=SUMPRODUCT(($A$3:$A$8=H3)*($B$3:$B$8=I3)*($C$3:$C$8=J3)*$D$3:$D$8)
An alternative could be above formula.
Apr 11 2022 07:39 PM
Thanks very much @OliverScheurich and @Riny_van_Eekelen - I ran into trouble because there were also duplicate entries, so their prices were being added together to give the price. But I've worked out how to remove them and now it works :D Appreciate your time!