Sumif or sumproduct across multiple sheets with multiple criteria

[stirlingmw1]

Good Morning all

I have a workbook that I am using to forecast training ammunition numbers for multiple ships I manage. I am using a Userform to select the year I wish to forecast for, then the ship I am forecasting for. Once these two parameters are selected I selected "Generate New". This generates a new Worksheet (if not already present) using the Ship Name and Year as the worksheet name i.e. "BROC_20-21". I then proceed to annotate a program for each month that drives a certain number of each ammunition type. When a program for 12 months has been completed I select "Add" button. This then sends the selected data to the worksheet with the program name for each month in row 1 (C1 to N1) C2 to N2 has the Months April to March then the selected ammunition totals for each month are added from C3 to C28 across to N3 to N28.

All of this works fine, but one of the Worksheets generated the first time a new year range is selected (e.g. FOST_20-21_Consolidated) needs to total all ammunition Quantities if the program in row 1 includes either the text "MCM OST", "MCM DCT", "OPVB1 OST" ....... I am hoping to use "* OST", "* DCT" etc if possible.
So if BROC_20-21 April program (C1) has MCM OST in it and CATT_20-21 April program (C1) has MCM OST in it, then FOST_20-21_Consolidated C3 to C28 has the sum of each of the Broc and Catt C3 to C28.

What I am intending to do is have the final formula added whenever a new sheet is added to include the new worksheet name.

something similar to

Worksheets("FOST_" & CmboYear.Value & "Consolidated").Range("O3:O" & Lastrow).Formula = "=Sum" & Worksheets("Admin").Range("Part1") & CmboYear.Value & Worksheets("Admin").Range("Part2") & "O" & ActiveCell.Row + 2 & ")"

Part1 = ('*_ this is a named cell I20 in "Admin" worksheet
Part2 = '! this is a named cell J20 in "Admin" worksheet


Any ideas, or am I over complicating things?

Thanks

Steve