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?

 

[Waimea]

Hi again,

say I have 22 different datasets, how should I go about then?

 

[Swayzy]

Hi again,

say I have 22 different datasets, how should I go about then?

Without seeing the actual data structure it would be hard to give a good recomendation. There are alot of ways to get to but depending on circumstances the solution might differ quite a bit. For example sometimes a quick and dirty solution is ok since it is a one time thing or if is going to be heavily used then a more reliable solution is prefered.

Feel free to post a mock up file with no sensitive data that I can look at

 

[Peter_SSs]

say I have 22 different datasets, how should I go about then?

1. Are they all just different years like you were discussing in post 1?

2. If you are still trying to do SUMIFS, could we see say two of the individual SUMIFS formulas that you would be using?

3. Do you have 'formal' Excel tables as Swayzy was describing in post 4?
- If not is there any reason you couldn't change to that?
- If they are formal tables, what are the table names? That is, is there a pattern to the table names?

4. Are the tables all in one sheet?
- If so is there a pattern about their location? (eg they all start in row 1 and start in every 10 columns (Column A, then K then U etc?
- If they are all on separate sheets, what are the sheet names (eg are they years like 2017 or 2018) and are the tables located in the same position in each sheet?

5. How many (approximately) formulas will you have that have to calculate from one of the tables depending on the drop-down?

@Swayzy
Regarding your reply at post 8, please refer to # 4 of the Forum Rules, in particular the last 2 paragraphs. Whilst we wouldn't prohibit you making that file and linking to it, we would still like any explanation and formulas published here in the thread where possible.

 

[Swayzy]

.....
@Swayzy
Regarding your reply at post 8, please refer to # 4 of the Forum Rules, in particular the last 2 paragraphs. Whilst we wouldn't prohibit you making that file and linking to it, we would still like any explanation and formulas published here in the thread where possible.

I just thought that I've already explained a possible solution quite clearly in my comments, this was just live a example. But anyway this is breakdown:

1 Create formal Excel tables
2 Name tables exactly the same but use the relevant year as ending. For example "Sales2019"
3 Create a separate formal table with all relevant years. Select a cell and open the data validation query and select the new column with years. Data validation range will auto expand as you expand the table if you need more years
4 The reference to the Indirect function should look like this ="Sales"&cell ref were you have the year selection&"[Net Sales]". Between the brackets you need new name of the column you want to reference, you can also use that as a cell reference if you want to sum over different columns.
5 Now you can put the indirect function as the range arguments in the sumifs formulas.

 

[Waimea]

1. Are they all just different years like you were discussing in post 1?

2. If you are still trying to do SUMIFS, could we see say two of the individual SUMIFS formulas that you would be using?

3. Do you have 'formal' Excel tables as Swayzy was describing in post 4?
- If not is there any reason you couldn't change to that?
- If they are formal tables, what are the table names? That is, is there a pattern to the table names?

4. Are the tables all in one sheet?
- If so is there a pattern about their location? (eg they all start in row 1 and start in every 10 columns (Column A, then K then U etc?
- If they are all on separate sheets, what are the sheet names (eg are they years like 2017 or 2018) and are the tables located in the same position in each sheet?

5. How many (approximately) formulas will you have that have to calculate from one of the tables depending on the drop-down?

1. They are different years and different content.

2. I am trying to do sumifs.

SUMIFS(AMOUNT; COUNTRY-CRITERIA1, COUNTRY1)

3. I am using tables and named ranges because the named ranges are easier to type.

4. They are in different sheets, they have different locations and the sheet names are different.

5. I am not sure, perhaps 100?

 

[Peter_SSs]

3. I am using tables and named ranges because the named ranges are easier to type.

4. They are in different sheets, they have different locations and the sheet names are different.

I think if you

a) Convert all those tables to 'formal' tables (if not already) by selecting the whole table then Insert ribbon tab -> Table -> My table has Headers -> OK

b) Ensure that the table name used a standard format. You can rename any table via the Name Manager on the Formulas ribbon tab - Select the table in the Name manager -> Edit. The format I used was to name each table "T_2017", T_2018" etc to reflect the year it applies to.

c) Ensure that each table has the amount column that you want to use in the SUMIFS formula named identically (I used "Amount") and same for "Country"

Here are 2 of my tables in different positions on different sheets and note that the tables happen to have a different number of columns and the columns of interest are not in the same positions

Excel Workbook
	A	B	C
1	Date	Amount	Country
2	1/01/2017	2	Nepal
3	2/01/2017	3	Austria
4	3/01/2017	5	France
5	4/01/2017	6	Nepal
6	5/01/2017	3	France
7	6/01/2017	2	Austria
8	7/01/2017	1	Austria
9	8/01/2017	5	Nepal
2017

Excel Workbook
	I	J	K	L
13	Country	Date	Other	Amount
14	India	22/12/2018	data 1	8
15	Nepal	5/05/2018	data 2	5
16	France	5/05/2018	data 3	6
17	Nepal	24/03/2018	data 4	3
2018

Now when I want to do the sumifs on another sheet, I have the year of interest in A1, Country of interest in A2 then formula in A3 to do the SUMIFS from whatever year you have chosen. Simply change A1 or A2 to get the result for a different year or different country.
This way you can have as many years as you want in similar tables and the formula in A3 does not have to change.

Excel Workbook
	A
1	2018
2	Nepal
3	8
Result

 

[Waimea]

Hi Peter_SSs ,

thank you very much for your explanation. I see what you are doing with INDIRECT!

 

[Peter_SSs]

Hi Peter_SSs ,

thank you very much for your explanation. I see what you are doing with INDIRECT!

Cheers. Good luck with your project.

 

[Waimea]

I will take a closer look again tomorrow. I think that your solution is very clever and I will use it.

Thanks again Peter_SSs.