Hi
I don't normally post in forums as generally find someone else has already asked/answered my questions but I just can't solve/find the answer to this one so would appreciate any help you can give! I also was not able to download the .xla to post a table using the required system so have tried to improvie below.
I am using Excel 2010 and trying to do a SUMIFS formula to return the sum value for all possible combinations of the multiple values entered as criteria.
E.g. below is the example data set (real data set is hundreds of lines long and using pivots is NOT an option as I need to the sum the data up different ways on different tabs. Data tab is a download from BW and on a tab on its own, Projects that need to pull from the BW data are on other tabs. in additon to below data & criteria I have year and month in my real data which are also criteria in the look up)
_____ A ___________B __________C _______D
1 ____Material ______Vendor_____ Plant_____Spend
2 ____123________ 234 ______1________100
3____123 __________234________ 2 ________150
4____321 __________234________ 2 ________110
5____123 __________245 ________1________ 120
6____321 __________111________2 ________130
7____321 __________111 ________1 ________101
8____123 __________111 ________1 ________100
9____321__________ 124 ________2 ________110
10 ___543 __________259 ________1 ________120
11 ___543 __________234 ________1 ________130
12
13 Criteria
14 ____Plant__________1
15 __________________2
16 ____Vendor _______234
17__________________111
18____Material _______123
19__________________321
I want to use a SUMIFS formula to return the spend for all combinations of Material 123 or 234 and vendor 234 or 111 and plant 1 or 2.
When I try the formula
=SUMPRODUCT(SUMIFS($D$2:$D$11,$a$2:$A$11,B18:B19,$B$2:$B$11,B14:B15,$C$2:$C$11,B14:B15))
I only get the result 230. That is the red bold rows. But I want it to return the results for ALL the red rows (which should be 691).
I tried with an OR formula in the criteria part but can't get it to work. While searching the internet I saw some posts about the { } to simulate the OR funcation but can't get that to work for the multiple OR criteria I have. I can get it to work if I have 2 vendors and one material and one plant.. But not if there are 2 vendors & 2 materials & 2 plants (so 6 possible unique combinations of the variables).
Any help by anyone that can get it to work without having to do multiple sumifs statements would be greatly appreciated?. I currenlty have it set up with multiple sumifs but it would be much more userfiendly if I could get it work as I want so it is easier for the end user of the workbook to use and maintain as new project tabs are added to the file.
Thanks in advance for your help!!
Emma
I don't normally post in forums as generally find someone else has already asked/answered my questions but I just can't solve/find the answer to this one so would appreciate any help you can give! I also was not able to download the .xla to post a table using the required system so have tried to improvie below.
I am using Excel 2010 and trying to do a SUMIFS formula to return the sum value for all possible combinations of the multiple values entered as criteria.
E.g. below is the example data set (real data set is hundreds of lines long and using pivots is NOT an option as I need to the sum the data up different ways on different tabs. Data tab is a download from BW and on a tab on its own, Projects that need to pull from the BW data are on other tabs. in additon to below data & criteria I have year and month in my real data which are also criteria in the look up)
_____ A ___________B __________C _______D
1 ____Material ______Vendor_____ Plant_____Spend
2 ____123________ 234 ______1________100
3____123 __________234________ 2 ________150
4____321 __________234________ 2 ________110
5____123 __________245 ________1________ 120
6____321 __________111________2 ________130
7____321 __________111 ________1 ________101
8____123 __________111 ________1 ________100
9____321__________ 124 ________2 ________110
10 ___543 __________259 ________1 ________120
11 ___543 __________234 ________1 ________130
12
13 Criteria
14 ____Plant__________1
15 __________________2
16 ____Vendor _______234
17__________________111
18____Material _______123
19__________________321
I want to use a SUMIFS formula to return the spend for all combinations of Material 123 or 234 and vendor 234 or 111 and plant 1 or 2.
When I try the formula
=SUMPRODUCT(SUMIFS($D$2:$D$11,$a$2:$A$11,B18:B19,$B$2:$B$11,B14:B15,$C$2:$C$11,B14:B15))
I only get the result 230. That is the red bold rows. But I want it to return the results for ALL the red rows (which should be 691).
I tried with an OR formula in the criteria part but can't get it to work. While searching the internet I saw some posts about the { } to simulate the OR funcation but can't get that to work for the multiple OR criteria I have. I can get it to work if I have 2 vendors and one material and one plant.. But not if there are 2 vendors & 2 materials & 2 plants (so 6 possible unique combinations of the variables).
Any help by anyone that can get it to work without having to do multiple sumifs statements would be greatly appreciated?. I currenlty have it set up with multiple sumifs but it would be much more userfiendly if I could get it work as I want so it is easier for the end user of the workbook to use and maintain as new project tabs are added to the file.
Thanks in advance for your help!!
Emma
