Sumif or sumproduct across multiple sheets with multiple criteria

stirlingmw1

New Member
Joined
Jun 17, 2016
Messages
25
Office Version
  1. 2016
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
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
VBA Code:
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Watch MrExcel Video

Forum statistics

Threads
1,133,271
Messages
5,657,765
Members
418,411
Latest member
Excellency

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top