SUM from multiple sheets for multiple out of order rows

Occasional Visitor

Beginner level user here:

 

The goal is to have the sum of all "like part numbers" and then multiply by a value on the final work sheet. I am looking for a way to avoid typing a formula 250 times.

 

I have multiple worksheets, most of which contain the same part numbers (different quantities), they are in different rows due to the volume of parts in each worksheet and some unique part numbers in between.

 

Everything is sorted the same, but due to the fact there are some unique part numbers in between on each sheet, I cannot simply drag my formula down to fill all 250 rows. 

 

Thanks!

 

1 Reply

@CKostohris 

=SUM(($A$2:$A$20=J2)*$B$2:$B$20,($D$2:$D$20=J2)*$E$2:$E$20,($G$2:$G$20=J2)*$H$2:$H$20)

Does this basically do what you are looking for? Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. I understand that your tables are in different worksheets but it's easy to adapt the formula if it returns your expected result in the attached example.