# SUM on Every Sheet that Totals a Cell in Every Sheet With Same First 3 Letters of Own Name

##### New Member
Hi all. Thank you for any help with this. New to the forum and stumped with this - not sure if it's best done with a SUM formula or with VBA instead.

I have a workbook with sheet (tab) names like so:

AAA00001, AAA00002, AAA0000A, BBB0000X, BBB00003, etc., etc.

On each of these sheets, I have a cell (let's say A1) that indicates the group which the sheet falls into. For example, all of the AAA sheets value at A1 would be 3 characters 'AAA'. All of the BBB sheets have value 'BBB' in A1 cell... and so on.

In cell B2 of each and every sheet is a dollar value.

I am wanting a formula (in say cell C3) that totals all the dollar values of every single sheet that exists (order is mixed up) which has the same first 3 characters (AAA, BBB or XXX, etc.). Noting that I can reference all tabs also using the A1 reference that exists on each of the sheets.

So, on any of the AAA, BBC (or whatever 3 letter prefix sheets) I can see a total of each group dollar value.

I hope I have been clear enough and I would greatly appreciate help.

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Hey! I am not an expert at all - but a quick google search on your topic lead me to these two formulas using VLOOKUP:

1. Summing up quantities using VLOOKUP: https://www.ablebits.com/office-addins-blog/2014/08/05/excel-vlookup-sum-sumif/
2. Indexing entire xls workbook using VLOOKUP: https://www.xelplus.com/lookup-values-across-multiple-worksheets-excel/

It looks like the key is something about the INDIRECT function - substitute it for when excel asks you for an array. I'm super tired now - or else I'd test it out myself with you. Let me know if it works.

Cheers mate.

Thank you. Will have a go at it.

Replies
4
Views
168
Replies
5
Views
190
Replies
1
Views
97
Replies
2
Views
123
Replies
0
Views
103

1,203,754
Messages
6,057,156
Members
444,908
Latest member
Jayrey

### 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.

### Which adblocker are you using?

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

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