How to use SUMIFS on two different data tables?

[Waimea]

Hi,

I have two data tables, one for 2017 and one for 2018. Next year I'll have another table for 2019.

I am using named ranges for column names and I am using the named ranges in SUMIFS formula.

However, since there are two different tables I can't figure out how to use SUMIFS to either check data for 2017 och for 2018.

Data table 1 has a column named years in it and all entries are 2017. And data table 2 has a column named years in it and all entries are 2018.


How can I use nested if statements to SUMIFS from 2017 table or from 2018 table, depending on a activex dropdown that is named DashboardYears.

So IF I select 2017 I want to SUMIFS from the 2017 year table. IF I select 2018 I want to SUMIFS from the 2018 year table.

Is there a good way to do this?




Ps. I have though about a mastertable with 2017 and 2018 year tables are in the same table but I want to keep them separate.

=IF(DashboardYears="2017"; Value if true; Value if false )

=IF(DashboardYears="2017"; SUMIFS for 2017;  SUMIFS FOR 2018)

But I am not sure how this would work for 2019 also?

 

[Swayzy]

Depending on how many individual sumifs you need I would suggest you try Indirect function for the ranges. If you are using proper tables just build the columns you need to ref in text and add the year variable as a cell reference inside the concatenated string. Then use the string in your indirect functions

 

[Waimea]

Hi Swayzy,

thank you for your reply!

I am using

=OFFSET(2017!$D$2; 0; 0; COUNTA(2017!$C:$C)-2; 1)

To get my named ranges,

what I am asking is how to use IF formula or other formula to get the right data when I select the activex combobox?

 

[Swayzy]

If you need the data in separate tables I would advise you to use the real table function (ctrl + t). Doing so makes it easier to reference them and the ranges auto expand when you enter new data. Hassle free and easy for other to follow.

If you have different named ranges for the different sheets then I would create a lookup table with years in the first column and the named ranges in the second. Then use an index match inside the indirect function that fetches the right range name.

If you havent already looked into indirect I suggest you do so. It can take a string that looks like a range reference and pass it to functions as a true range.

 

[Waimea]

Hi Swayzy,

thank you for your reply and for your suggestions!

I know some things about INDIRECT function and I will try it out!

Could you explain more about your lookup table? (With years in the first column and the named ranges in the second column)

 

[Swayzy]

When a user selects a year to look at and you have the data in separate ranges/sheets then you need to alter the referenced named ranges. So if a user switches from 2018 to 2019 then the lookup inside the indirect Will Go to this table and fetch the corresponding named range. If the named ranges are intuitive then you can use just One column with those names. If you use a true table for the reference list then you can add a name and the dropdown Will expand automatically. The table function is there to replace simple named ranges of data tables.

For instance in your situation you have same type of data for each year but you want it separate. Then you would only need to copy the table, rename it to reflect a new year. Then you can through the indirect function build the reference string by spiltting the string at the year part and there you reference the selected year. Then everthing is very easy to update. I can post more exact tomorrow when I am at my computer

 

[Waimea]

Hello Swayzy,

thank you for your reply!

I think I understand what you mean and I would very much like another post from you tomorrow when you are near a computer!

 

[Swayzy]

Hello Swayzy,

thank you for your reply!

I think I understand what you mean and I would very much like another post from you tomorrow when you are near a computer!

I made a simple mockup file. Here you should be able to follow the example and see why this is a very optimized structure as long as you dont try to build a big table with the sum functions that use indirect. So if you have a summary overview that you want to be able to easy go back in and check the history the only thing you need to do is when you copy either the entire sheet or just the data table is to change the name to include the new year and be sure that the new year gets added to reference.

https://www.dropbox.com/s/fs5x76pm3s6gq3x/Sum with indirect.xlsx?dl=0

 

[Waimea]

Hi Swayzy,

thank you for your reply!

I see what you did there and I got it to work with your suggestion!

 

[Swayzy]

Glad I could help!!