CODEWITHSUNDEEP
google-sheets-formulaarray-formulassumifs
Cate Kemp SBFO
Cate Kemp SBFO

Reputation: 13

SUMIF (INDIRECT()) within ARRAYFORMULA() Google Sheets

I'm trying to wrap a sumif(range) formula in an array formula. The 'range' is dynamic, and is derived by an INDIRECT(ADDRESS()) combo, based on an input cell in this case $D$2. I used this approach as R1C1 notation didn't work when referencing another sheet within an arrayformula.

The formula references the an input date ($D$2), and sums the value in each row, up to the lookup date(column).

I need to wrap the formula in an ARRAYFORMULA

The following formula works perfectly and returns a sum from the dynamic range.

=(IF(B6:B<>"", IF((isnumber(search("** Expenses",B6:B))=FALSE)+(B6:B:B="Total Expenses"), SUMIF(INDIRECT("Actual P&L!" & ADDRESS(MATCH(B6:B,'Actual P&L'!B:B,0),3,4,TRUE)&":"&ADDRESS(MATCH(B6:B,'Actual P&L'!B:B,0),MATCH(TEXT($D$2,"Mmm-YY"), 'Actual P&L'!$8:$8, 0),4,TRUE),TRUE),">=0"), SUMIF(INDIRECT(ADDRESS(MATCH("Total "&B6:B,'Actual P&L'!B:B,0),3,4,TRUE,"Actual P&L")&":"&ADDRESS(MATCH("Total "&B6:B,'Actual P&L'!B:B,0),MATCH(TEXT($D$2,"Mmm-YY"), 'Actual P&L'!$8:$8, 0),4,TRUE),TRUE),">=0")), ""))

for the purposes of this, I'll simplify it to;

=(IF(B6:B<>"",SUMIF(INDIRECT("Actual P&L!" & ADDRESS(MATCH(B6:B,'Actual P&L'!B:B,0),3,4,TRUE)&":"&ADDRESS(MATCH(B6:B,'Actual P&L'!B:B,0),MATCH(TEXT($D$2,"Mmm-YY"), 'Actual P&L'!$8:$8, 0),4,TRUE),TRUE),">=0"),"")

The IF() statement exists to get the formula to look at each row (and to skip rows that don't have data)

When I wrap it in an ARRAYFORMULA I just get 'zero' as the array output, although it does populate the rows and skips the blanks.

=arrayformula(if(B6:B<>"",SUMIF(INDIRECT("Actual P&L!" & ADDRESS(MATCH(B6:B,'Actual P&L'!B:B,0),3,4,TRUE)&":"&ADDRESS(MATCH(B6:B,'Actual P&L'!B:B,0),MATCH(TEXT($D$2,"Mmm-YY"), 'Actual P&L'!$8:$8, 0),4,TRUE),TRUE),">=0"),""))

I have a dummy sheet HERE

Upvotes: 1

Views: 647

Answers (1)

rockinfreakshow
rockinfreakshow

Reputation: 29982

You may try:

=byrow(B6:B,lambda(z,if(z="",,sum(ifna(filter(filter('Actual P&L'!C9:N,eomonth("1-"&'Actual P&L'!C8:N8,)<=eomonth(D2,)),'Actual P&L'!B9:B=z))))))

enter image description here

Upvotes: 0

Related Questions