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

[Aden Wessels]

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.

 

[D1XII]

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.

 

[Aden Wessels]

Thank you. Will have a go at it.