# Calculating Totals from dynamic table

1. ## Calculating Totals from dynamic table

Hi Everyone,

I have looked though this forum but i think i am facing a unique issue that i need your help with.

In the attached file, there are two tabs, data and data summary. I want the data summary tab to pick up the yearly totals from the "Data" tab.

However, there are two issues:

1- the table in the Data tab varies from month to month (Columns and rows move, and are deleted/ added)
2- The data i need is dependent on 3 variables: The Org, Currency and the total.

For Example of Cell C9 of the data tab, i need excel to look at the table (which changes each month), narrow down org. 2100 and USD and then return the total for 2018.

I would really appreciate your help with this! Thank you in advance!!

Sohshak  Register To Reply

2. ## Re: Calculating Totals from dynamic table

Sheet "Data" has a single occurrence of each Org and Currency (one-to-one.)
Sheet "Data Summary" has multiple repeat occurrences of each currency but NO occurrences of orgs.

Therefore, there is no way to breakdown the currencies per org.  Register To Reply

3. ## Re: Calculating Totals from dynamic table

Sorry, i forgot to add. Sometimes there are multiple occurrences of org and Currency whereas sometimes its one org and one currency.

In this case, its possible for org. wth no listed currencies to equal zero through an iferror formula so that the data summary tab picks up the information it needs and zero's out the information that is not available?

Thanks,

sohshak  Register To Reply

4. ## Re: Calculating Totals from dynamic table

The summary sheet must specify the org(s) somewhere. Please post a more representative example file. As it is, you can only match currency and year:  Register To Reply

5. ## Re: Calculating Totals from dynamic table

Okay, find attached a more representative example spreadsheet.

I have manually filled out the data summary tab for 2018 for the first two companies. I want to automate this process so that excel can recognize that Org. 2100 with currency of TLR has a 2018 total of 200.  Register To Reply

6. ## Re: Calculating Totals from dynamic table

Dynamic Named Range:
CurrencyByMonth =' DATA'!\$A\$8:INDEX(' DATA'!\$1:\$1048576,COUNTA(' DATA'!\$A:\$A)+4,COUNTA(' DATA'!\$8:\$8))

CurrencyCol =2
DateRow =1
OrgCol =1

Summary Formula: ``Please Login or Register  to view this content.``
With the new org data a pivot table works well.  Register To Reply

7. ## Re: Calculating Totals from dynamic table

another way you can use PowerQuery and PivotTable or simply even PivotTable

Power Query for  Register To Reply