SUMPRODUCT with multiple criterias

Jerseey

New Member
Joined
Apr 20, 2021
Messages
8
Office Version
  1. 365
Hi,

I have to files:
  • XXX.xlsx - File with one tab per department (A, B, C), each with amounts per GL Accounts and months.
    XXX.xlsx.png


  • Report.xlsx - FIle with two tabs, one report tab and one tab with GL accounts per phase stored in a table (T_PhaseGL).
    Report.png


    Accounts per Phase.png


    The Report referances the XXX.xlsx file, but the file changes every month. So I want to create a dynamic report, where I add the file name in cell B3 and the period I want in B4. I have created a a formula that handles this for specific accounts.

    =INDEX(INDIRECT("["&$B$3&"]"&$A7&"!$B$4:$M$12");MATCH(700000;INDIRECT("["&$B$3&"]"&$A7&"!$A$4:$A$12");0);MATCH($B$4;INDIRECT("["&$B$3&"]"&$A7&"!$B$3:$M$3")))

    However, I have to add it manually for each GL account I want to include for each phase.

    So my plan was to add all the accounts in the T_PhaseGL that I want for each phase. I was hoping to use a SUMPRODUCT formula that checks if the GL account in XXX.xlsx is listed in T_PhaseGL[Tender], struggeling to find a solutions where there are multiple matches.

    Can anyone help me find a solution?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
There may be a better way to do this with the improved function set in Excel 365, but here is one approach using an array formula (you won't have to confirm this formula with Control-Shift-Enter in 365). The notional set of data used for checking out the formula appear on tabs A, B, and C further below.

Report.xlsx
ABCD
1Report
2
3File RefXXX.xlsx
4PeriodMar-21
5
6DeptTenderExecutionAftermarked
7A297120
8B307023
9C377810
report
Cell Formulas
RangeFormula
B7:D9B7=SUM(SUMIF(INDIRECT("["&$B$3&"]"&$A7&"!$A$4:$A$12"), IFERROR(INDEX('GL accounts'!$A$4:$C$8,,MATCH(report!B$6,'GL accounts'!$A$3:$C$3,0)),0), INDEX(INDIRECT("["&$B$3&"]"&$A7&"!$B$4:$M$12"),,MATCH($B$4,INDIRECT("["&$B$3&"]"&$A7&"!$B$3:$M$3"),0))))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.


Report.xlsx
ABC
1Accounts Per Phase
2
3TenderExecutionAftermarked
4200000100000700000
5400000300000
6500000
7800000
8900000
GL accounts


XXX.xlsx
ABCDEFGHIJKLM
1Department A
2
3GL AcctJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21
410000010711108201532221019
52000002210791082015322228
63000003222107910820153237
74000001532221079108201546
85000002015322210791082055
9600000820153222107910864
107000001082015322210791073
11800000910820153222107982
12900000791082015322210791
A


XXX.xlsx
ABCDEFGHIJKLM
1Department B
2
3GL AcctJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21
41000007109108201532221019
52000001022791082015322228
63000002232107910820153237
74000003217231079108201546
85000001520332210791082055
9600000208153222107910864
107000008102315322210791073
11800000109820153222107982
12900000971082015322210791
B


XXX.xlsx
ABCDEFGHIJKLM
1Department C
2
3GL AcctJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21
41000009710108201532221019
52000007102291082015322228
63000001022327910820153237
74000002232151079108201546
85000003215202210791082055
9600000152083222107910864
107000002081015322210791073
11800000810920153222107982
12900000109782015322210791
C
 
Upvote 0
We use Office 365, but one version with limitations, so this I have to use CTRL + SHIFT + ENTER on this formula. I was hoping to avoid array formulas. Any other possibilities?
 
Upvote 0
I don't see a convenient way to do it without an array formula. The yellow cells comes close, but I've manually inserted the arrays that are needed to make the formula work. Taking cell B9 as an example, I inserted {0;1;0;1;0;0;0;0;0}. That needed array would have to be generated by looking at the first column of the appropriate worksheet in XXX.xlsx, for example...
INDIRECT("["&$B$3&"]"&$A9&"!$A$4:$A$12")
which will return...
{100000;200000;300000;400000;500000;600000;700000;800000;900000}
And then we need to determine whether each value in this array is found in the array formed from the appropriate column of the "GL accounts" sheet. Since this particular yellow cell is associated with "Tender", this part of the formula...
INDEX('GL accounts'!$A$4:$C$8,,MATCH(report!B$6,'GL accounts'!$A$3:$C$3,0))
will return...
{200000;400000;0;0;0}

So we need to cycle through the arrays multiple times...the 1st time we learn that 100000 does not match anything in the "GL accounts" array, so a 0 is returned...the 2nd time we learn that 200000 is found in the "GL accounts" array so a 1 is returned...and so on to generate the array, {0;1;0;1;0;0;0;0;0}. I don't see a way to do that without an array formula, but I might be overlooking a clever approach. If a way can be found, then that expression could be substituted into the SUMPRODUCT formula in place of {0;1;0;1;0;0;0;0;0} and the result should be the desired non-array version.
Report.xlsx
ABCD
1Report
2
3File RefXXX.xlsx
4PeriodMar-21
5
6DeptTenderExecutionAftermarked
7A297120
8B307023
9C377810
report2
Cell Formulas
RangeFormula
B7:D8B7=SUM(SUMIF(INDIRECT("["&$B$3&"]"&$A7&"!$A$4:$A$12"), IFERROR(INDEX('GL accounts'!$A$4:$C$8,,MATCH(report!B$6,'GL accounts'!$A$3:$C$3,0)),0), INDEX(INDIRECT("["&$B$3&"]"&$A7&"!$B$4:$M$12"),,MATCH($B$4,INDIRECT("["&$B$3&"]"&$A7&"!$B$3:$M$3"),0))))
B9B9=SUMPRODUCT(INDIRECT("["&$B$3&"]"&$A9&"!$B$4:$M$12"),--(INDIRECT("["&$B$3&"]"&$A9&"!$B$3:$M$3")=$B$4)*{0;1;0;1;0;0;0;0;0})
C9C9=SUMPRODUCT(INDIRECT("["&$B$3&"]"&$A9&"!$B$4:$M$12"),--(INDIRECT("["&$B$3&"]"&$A9&"!$B$3:$M$3")=$B$4)*{1;0;1;0;1;0;0;1;1})
D9D9=SUMPRODUCT(INDIRECT("["&$B$3&"]"&$A9&"!$B$4:$M$12"),--(INDIRECT("["&$B$3&"]"&$A9&"!$B$3:$M$3")=$B$4)*{0;0;0;0;0;0;1;0;0})
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 
Upvote 0
After revisiting this, I arrived at the following for forming the array I mentioned in my last post:
Excel Formula:
COUNTIF(INDEX('GL accounts'!$A$4:$C$8,,MATCH(B$6,'GL accounts'!$A$3:$C$3,0)), INDIRECT("["&$B$3&"]"&$A7&"!$A$4:$A$12"))
Substituting that into the SUMPRODUCT approach results in the solution below. Give this a try and see if it works okay:
Report.xlsx
ABCD
1Report
2
3File RefXXX.xlsx
4PeriodMar-21
5
6DeptTenderExecutionAftermarked
7A297120
8B307023
9C377810
report
Cell Formulas
RangeFormula
B7:D9B7=SUMPRODUCT(INDIRECT("["&$B$3&"]"&$A7&"!$B$4:$M$12"),--(INDIRECT("["&$B$3&"]"&$A7&"!$B$3:$M$3")=$B$4)*COUNTIF(INDEX('GL accounts'!$A$4:$C$8,,MATCH(B$6,'GL accounts'!$A$3:$C$3,0)), INDIRECT("["&$B$3&"]"&$A7&"!$A$4:$A$12")))
 
Upvote 0
Solution
After revisiting this, I arrived at the following for forming the array I mentioned in my last post:
Excel Formula:
COUNTIF(INDEX('GL accounts'!$A$4:$C$8,,MATCH(B$6,'GL accounts'!$A$3:$C$3,0)), INDIRECT("["&$B$3&"]"&$A7&"!$A$4:$A$12"))
Substituting that into the SUMPRODUCT approach results in the solution below. Give this a try and see if it works okay:
Report.xlsx
ABCD
1Report
2
3File RefXXX.xlsx
4PeriodMar-21
5
6DeptTenderExecutionAftermarked
7A297120
8B307023
9C377810
report
Cell Formulas
RangeFormula
B7:D9B7=SUMPRODUCT(INDIRECT("["&$B$3&"]"&$A7&"!$B$4:$M$12"),--(INDIRECT("["&$B$3&"]"&$A7&"!$B$3:$M$3")=$B$4)*COUNTIF(INDEX('GL accounts'!$A$4:$C$8,,MATCH(B$6,'GL accounts'!$A$3:$C$3,0)), INDIRECT("["&$B$3&"]"&$A7&"!$A$4:$A$12")))
Thanks, this works!
 
Upvote 0
Thanks, this works!
Glad to hear you received a working solution.

Next time, that would be great if you could mark the solution post instead of your final post confirming the actual answer. I switched the answer for this post as you also confirmed, so that would help future readers better. Thanks.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top