Suming a table total based on Defined Name and month

Hi
As per attached workbook, I would like an Excel formula to pick up a group of account number from a defined name and add all the amounts for a particular month.

There will be a number of defined names which could more than 10 different account numbers.

In the example:
Defined Name Relates to Account
Acct1 5000, 5100 and 5300

The month required is for May
So the total is 195 + 205 + 225 = 625

Thanks.

Thanks Samba

Hi Samba
Continuing from the above, is it possible to get a total for the for Acct1 from Jan to May?

So the total for Acct1 will be total of the followings:
Account# 5000 = 115+135+155+175+195
Account# 5100 = 125+145+165+185+205
Account# 5200 = 145+165+185+205+225

So the total for Acct1 is 2525
The total for Accnt1 will need to base on the Account numbers in Acct1 and the Month (total of all the month before and including current month).

Thanks.

Hi Samba
I am also having issues with using indirect (reference to another workbook) with =SUMPRODUCT(($A$3:$A$15=TRANSPOSE(Acct1))*INDEX($B$3:$M$15,,MATCH(C19,$B$2:$M$2,0)))

Thanks.

JeteMc and FlameRetired, thank you very much it worked perfectly. I could also change the starting month and it still works, that was what I was looking for.

Any idea how to use indirect (to reference to another workbook) with this formula? I have tried many combination and still unable to get it working.
=SUMPRODUCT(($A$3:$A$15=TRANSPOSE(Acct1))*INDEX($B$3:$M$15,,MATCH(C19,$B$2:$M$2,0)))

Thanks.

Glenn
Yes the workbook will be open.

If there is a better alternative instead of using indirect with another workbook, I will look at it as well.

I want to create one workbook where every month I will dump the monthly raw data into. There are four to six worksheets with raw data in this work book all from different sources.

I have a few other workbooks which produces different reports every month and extracting the raw data from the above workbook.

So by using indirect all the workbooks have to be opened.

Thanks.

Hi Glenn
I forgot to mention I know how to use Indirect, I use it to reference to a workbook and worksheet, but I am not sure how to combine
=INDIRECT("'[" & $C$2 & "]" & $C$3 & "'!" & ####)
=SUMPRODUCT(($A$3:$A$15=TRANSPOSE(Acct1))*INDEX($B$3:$M$15,,MATCH(C19,$B$2:$M$2,0)))
Note: #### can be replaced with formula or cell reference

I have tried putting indirect just after the sumproduct, just before transpose, before and after index, but unable to get it to work.

Any help will be great.

Thanks.

Hi Glenn
I have attached 2 workbooks. Save both workbooks on your computer and then open both workbooks.

Book1.xlsx
It has a table
3 different formulas:
- 1 is a simple sum formula
- 1 is a sum range of months based on certain accounts
- 1 is a month based on certain accounts

Book2.xlsx
Using indirect to refer to book1.xlsx table

I want to be able to use all 3 formulas to reference to book1.xlsx

The simple sum formula is in A8 and A9, which works.

The other 2 formulas I am not sure how to use indirect with the formulas

Is there a better way to use indirect in Book2.xlsx, currently I list the source workbook name and worksheet in B4 and B5.

Thanks.

Hi Glenn
Thank you very much for the quick reply. That is what I was looking for.

Over here our financial year starts in July and ends in June the following year. Apologies for the confusion.

Is this an efficient way to use indirect reference with the "source"? Or is there a better way to use indirect reference?

Thanks.

I prefer something simple and quick. So if I can, open only one workbook with multiple worksheets and extract the data from another workbook (not open) that will be great.

I won't be using a drop down to select specific workbook or sheet. The reason for having the "source" option with the workbook name and sheet name is that every year, i will create a new workbooks, eg. new worbook for book1.xlsx and book2.xlsx

I think for me to use reference from one workbook to another workbook, I have to use indirect and open all workbooks as a requirement for indirect to work.

I think there is another option is to use vba, which I do not have any knowledge of.

If there is a better option I like to explore as well.

Thanks.