Lightest formula for sumifs with multiple criteria

[cgsierra]

Hello,
I have a database set up in "Dbase" tab as follows:
Column A labeled LE, Column B labelled GTM, C labeled LOC, D labeled DEP, E labeled Amount. Each of these columns have over 20 different possible values. IE. LE (over 20 different possible values found in the database, ie 3,7,9,12,24,72,65,...), GTM (over 20 different possible values, ie 000,003,007,006,010,019,001,...), LOC (over 20 different possible values, ie 0101,0102,1245,2452,1110,0099,4215,...), DEP (over 20 different possible values, ie 1254,7845,0011,1289,6411,0001,4582,...)

I then have a second tab called "Output" set up as follows:
B3 = LE
B4 = GTM
B5 = LOC
B6 = DEP

range C3:F3 is used to type the multiple criteria used to look in Column A (LE) in the Dbase tab (4 or less possible criteria allowed, 1 in each column, ie C3=7, D3=12)

range C4:F4 is used to type the multiple criteria used to look in Column B (GTM) in the Dbase tab (4 or less possible criteria allowed, 1 in each column, ie C4=000, D4=019, E4=010, F4=003)

range C5:F5 is used to type the multiple criteria used to look in Column C (LOC) in the Dbase tab (4 or less possible criteria allowed, 1 in each column, ie C5=0101, D5=0102, E5=0099)

range C6:F6 is used to type the multiple criteria used to look in Column D (DEP) in the Dbase tab (4 or less possible criteria allowed, 1 in each column, ie C6=7845, D6=6411, E6=4582, F6=1289)

Once I enter the multiple criteria in the ranges mentioned above, the formula in cell b12 in tab "Output" should add column E (amount) for all the data in the Raw Data tab that fits the multiple criteria stated above.

I am looking for the lightest possible formula to execute this as the formula in b12 will be dragged over for about 500 rows and across 100 columns for a single sheet. The file itself may have about 10 of those Output sheets.

Please help!

 

[cgsierra]

Hello,
I have a database set up in "Dbase" tab as follows:
Column A labeled LE, Column B labelled GTM, C labeled LOC, D labeled DEP, E labeled Amount. Each of these columns have over 20 different possible values. IE. LE (over 20 different possible values found in the database, ie 3,7,9,12,24,72,65,...), GTM (over 20 different possible values, ie 000,003,007,006,010,019,001,...), LOC (over 20 different possible values, ie 0101,0102,1245,2452,1110,0099,4215,...), DEP (over 20 different possible values, ie 1254,7845,0011,1289,6411,0001,4582,...)

I then have a second tab called "Output" set up as follows:
B3 = LE
B4 = GTM
B5 = LOC
B6 = DEP

range C3:F3 is used to type the multiple criteria used to look in Column A (LE) in the Dbase tab (4 or less possible criteria allowed, 1 in each column, ie C3=7, D3=12)

range C4:F4 is used to type the multiple criteria used to look in Column B (GTM) in the Dbase tab (4 or less possible criteria allowed, 1 in each column, ie C4=000, D4=019, E4=010, F4=003)

range C5:F5 is used to type the multiple criteria used to look in Column C (LOC) in the Dbase tab (4 or less possible criteria allowed, 1 in each column, ie C5=0101, D5=0102, E5=0099)

range C6:F6 is used to type the multiple criteria used to look in Column D (DEP) in the Dbase tab (4 or less possible criteria allowed, 1 in each column, ie C6=7845, D6=6411, E6=4582, F6=1289)

Once I enter the multiple criteria in the ranges mentioned above, the formula in cell b12 in tab "Output" should add column E (amount) for all the data in the Raw Data tab that fits the multiple criteria stated above.

I am looking for the lightest possible formula to execute this as the formula in b12 will be dragged over for about 500 rows and across 100 columns for a single sheet. The file itself may have about 10 of those Output sheets.

Please help!

Please let me know if you have any questions or need me to explain further/better. Thank you kindly

 

[Eric W]

Are the values in LE, GTM, LOC, DEP numeric or alphanumeric? I see some of your examples have preceding zeros, which would imply alphanumeric. How many rows are in your database? Are there any empty cells? Unfortunately SUMIFS only allows 2 arrays, and you have 4. This will necessitate an array formula, which will not be "light". Would you consider a macro? Depending on how big your database is, we could set it up to run whenever any of the values in C3:F6 are changed.

And I don't understand where you want to drag this formula to. If they are refer to the same C3:F6, and the same columns on your DBase tab, they'll all return the same value.

 

[cgsierra]

Are the values in LE, GTM, LOC, DEP numeric or alphanumeric? I see some of your examples have preceding zeros, which would imply alphanumeric. How many rows are in your database? Are there any empty cells? Unfortunately SUMIFS only allows 2 arrays, and you have 4. This will necessitate an array formula, which will not be "light". Would you consider a macro? Depending on how big your database is, we could set it up to run whenever any of the values in C3:F6 are changed.

And I don't understand where you want to drag this formula to. If they are refer to the same C3:F6, and the same columns on your DBase tab, they'll all return the same value.

the values are alphanumeric. There are about 500K rows in the dbase. There wont be any empty cells in the dbase tab. However, the ranges where the multiple criteria is typed (tab Output) may have 1, 2,3 or 4 values, ie the LE criteria range may only have 2 values and the last 2 would be blank, the GTM criteria range may have 4 values, the LOC criteria range may have 3 values and the last cell of the range would be blank)....However, all four ranges can only have up to 4 criteria max. It would be great to be able to use the "*" wildcard as a criteria too. The macro sounds like a great idea. However, my knowledge is very limited in vba.

The actual data is actually 6 columns in the dbase (LE, RU, SL, GTM, LOC, DEP) instead of 4 and therefore the area where the multiple criteria range is set is also 6 rows instead of 4. I only provide 4 for simplification purposes.

The reason why I would copy it over is because I'm also using other variables such as month, scenario (actual, forecast, budget) as columns in the "Output" tab which will help me retrieve the figures for Jan 2018 through Mar 2018 Actual, Apr 2018 through Dec 2018 Forecast, Jan through Dec 2018 Budget and Jan through Dec 2017 Prior Year. I would drag it down because in each row under b12 I would have a different account number so I can have a full P&L by month, (ie. A12 would have account 40000, A13 would have account 40010, etc). Not sure how to share the actual file. My explanation seems convoluted.

 

[cgsierra]

the values are alphanumeric. There are about 500K rows in the dbase. There wont be any empty cells in the dbase tab. However, the ranges where the multiple criteria is typed (tab Output) may have 1, 2,3 or 4 values, ie the LE criteria range may only have 2 values and the last 2 would be blank, the GTM criteria range may have 4 values, the LOC criteria range may have 3 values and the last cell of the range would be blank)....However, all four ranges can only have up to 4 criteria max. It would be great to be able to use the "*" wildcard as a criteria too. The macro sounds like a great idea. However, my knowledge is very limited in vba.

The actual data is actually 6 columns in the dbase (LE, RU, SL, GTM, LOC, DEP) instead of 4 and therefore the area where the multiple criteria range is set is also 6 rows instead of 4. I only provide 4 for simplification purposes.

The reason why I would copy it over is because I'm also using other variables such as month, scenario (actual, forecast, budget) as columns in the "Output" tab which will help me retrieve the figures for Jan 2018 through Mar 2018 Actual, Apr 2018 through Dec 2018 Forecast, Jan through Dec 2018 Budget and Jan through Dec 2017 Prior Year. I would drag it down because in each row under b12 I would have a different account number so I can have a full P&L by month, (ie. A12 would have account 40000, A13 would have account 40010, etc). Not sure how to share the actual file. My explanation seems convoluted.

 

[cgsierra]

Tried sending pics for better explanation but i dont think it worked

 

[cgsierra]

the values are alphanumeric. There are about 500K rows in the dbase. There wont be any empty cells in the dbase tab. However, the ranges where the multiple criteria is typed (tab Output) may have 1, 2,3 or 4 values, ie the LE criteria range may only have 2 values and the last 2 would be blank, the GTM criteria range may have 4 values, the LOC criteria range may have 3 values and the last cell of the range would be blank)....However, all four ranges can only have up to 4 criteria max. It would be great to be able to use the "*" wildcard as a criteria too. The macro sounds like a great idea. However, my knowledge is very limited in vba.

The actual data is actually 6 columns in the dbase (LE, RU, SL, GTM, LOC, DEP) instead of 4 and therefore the area where the multiple criteria range is set is also 6 rows instead of 4. I only provide 4 for simplification purposes.

The reason why I would copy it over is because I'm also using other variables such as month, scenario (actual, forecast, budget) as columns in the "Output" tab which will help me retrieve the figures for Jan 2018 through Mar 2018 Actual, Apr 2018 through Dec 2018 Forecast, Jan through Dec 2018 Budget and Jan through Dec 2017 Prior Year. I would drag it down because in each row under b12 I would have a different account number so I can have a full P&L by month, (ie. A12 would have account 40000, A13 would have account 40010, etc). Not sure how to share the actual file. My explanation seems convoluted.

links to images

https://www.dropbox.com/s/ux46m5gkopbz30y/Raw%20Data%20Tab.JPG?dl=0
https://www.dropbox.com/s/02xdaqhih6npk6r/Output tab.JPG?dl=0

 

[iggy_]

I'm still not clear how the B3:B6 relates to the cells B12 and on. Can you describe that a little more. What is the formula in B12 now, is it a SUMIF that looks at B3:B6 and other cells?

 

[cgsierra]

I'm still not clear how the B3:B6 relates to the cells B12 and on. Can you describe that a little more. What is the formula in B12 now, is it a SUMIF that looks at B3:B6 and other cells?

These pics will probably help clarify:
Raw Data tab

Output tab (Sheet2)

 

[cgsierra]

The problem I have with the formula above is that it will slow down the performance of the file as it is too "heavy" and also this formula does not allow me to use wildcards (such as the ones in cells J2 and J3 of the Sheet2 tab)