Function/Formula like SUMIFS to include Sheet name

Hi,

I have a number of templates which have been sent out to various people and need a SUMIFS formula on a Summary Totals sheet after I have compiled them all together into one workbook.

Normally, I would just use the SUMIFS formula, however, because there are plenty of sheets to work with (after compiling all the templates into one worksheet), this would take a lot of effort and time to get this right.

Is there a way to create a function similar to a SUMIFS but specifying sheet names that include a specific word? e.g. if there are 20 sheets and 3 of them include "..."Priority Total" I want to sum only the sheets that include "Priority Total" in the sheet name.

Hope this makes sense.

Thanks for the quick response. Unfortunately, I can't seem to get this to work.

In the named range, do I need to make a complete list of ALL sheet names? If so, I'm not sure this would be a viable solution for me as there would be a large number of sheets to go through.

Another thing I should have mentioned is that I would also need to be able to sum the total of different sheet names, e.g. if someone wants to see the total of priority or non-priority (among other options) which they would select from a dropdown. I would also need to be able to see the total of ALL if required.

I've tried to attach an example but can't seem to do so. Perhaps a SUMIFS wouldn't be required as the sum would be of the same cell in each sheet. Each sheet is a table with, let's say, a month in column B along with the total in column C. The "Total" sheet is the same table as the others.

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi

I'm getting nothing when clicking the attach file icon in "Go Advanced". I see a "javascriptvoid('Attachments')" error. I've double checked and my browser allows for these scripts to run. I have a number of files uploaded when clicking on manage attachments.

Essentially, I have (or will have as I'm still collating them) several templates which I need to consolidate together. Each template will have more than one sheet with a suffix or prefix of, let's say, priority and non-priority.
The data will be in a table with months in cells C3:C14 and totals in cells E3:E14. The consolidated file has a summary tab with months in the same layout with an added dropdown above the data which has options priority/non-priority. If for example someone chooses priority from the dropdown, the formula in cell C3 of the summary sheet should be the sum of cell C3 from all sheets that have "priority" in the sheet name.

Hope that explains the issue.

I will have several sheets so going through each sheet or creating a list of each probably won't be the best way of solving the issue especially since I will be adding more sheets as the templates come in.

Thanks for the help so far.

I've managed to attach the file.

I've got 2 VBA modules which I found online but have no idea how to use them

Originally Posted by Glenn Kennedy

Macros removed. See sheet. happy to explain. I created some additional named ranges.

Hi Glenn,

Works great. However, I'm wondering whether there might be a solution where I don't have to add sheet names tot he named range as there will be several more templates coming in.

If not, I'll just have to add the sheet names as they come in.

Thanks

Originally Posted by Glenn Kennedy

Will the sheets be called XXX Priority or XXX non-priority, or can they be all sorts of things. can you be more explicit about sheet naming....

Hi Glenn,

At the moment, they have been coming in xxx non-priority/priority.

Great stuff . Thanks again.

I'll give it a go and let you know how it goes.

Hi Glenn

Perfect. Not sure how the Get.Workbooks bit works (not VBA/Macro savvy just yet) but I can work with this. I'll be saving this for future as I know it will come in handy at some point again.


Thanks for all your help and solving the issue for me.