Find-Search-2 Columns-Find / Search Multiple Criteria & Add or Sum Totals

Hi Everyone,

Working to verify quantities of key items within multiple Bills of Materials (BOMs)

In attached sample, you'll find BOMs for multiple assemblies, such as for:

0272L00906
0272L00907
0272L00908
0272L00909
0272L00910
0272L00911
Etc.

Objective is to find the key variations, listed in Summarized BOMs worksheet & find or match up items listed in column N, then search Multiple BOMs worksheet & sum the quantity totals per assembly

Within, Summarized BOMs is where I'm seekning quanity totals per assembly

For Capacitor, conducted manual searches for:

CAP040000370VA ( CAPACITOR )
CAP050000440LA ( CAPACITOR )
CAP100000370LA ( CAP, 10 MFD/370V/OVAL )


Looking to do similar searches but through an automated process for the 100s of other BOMs that I'll need to search later

In Cell F9 - Using to find, "Relay"
: =SUMPRODUCT(--(Multiple_BOMs[Qty]),--ISNUMBER(SEARCH(C9,(Multiple_BOMs[Top Level Assembly]),--ISNUMBER(SEARCH("10135604 ( RELAY ) ",(Multiple_BOMs[Sub Assembly - CP (Small)
Bill of Materials]))))))

This works nicely if only 1 criteria. If there are multiple & in some instances don't want to count items, like "bracket" - Useed for other serach criteria


How to modify this function to go through, Summarized BOMs worksheet or Assembly list in Column C & find variations defined in column N by extracting out of Multiple BOMs worksheet, then add or sum up total quantities?

Thanks

Bo_Ry

Great Solution for Semi-Complex Scenario!!!