I am using arrayformula with my sumif function which has several criterion and ranges to expand automatically across a set column (B). The formula below works perfectly:
=ARRAYFORMULA(SUMIF('DATA'!$N:$N&'DATA'!$U:$U&'DATA'!$V:$V&'DATA'!$BV:$BV,$B$6:$B&$E$30&$D$26&$C$36,'DATA'!$AG:$AG).
I want to make it more dynamic and insert an IF
statement to actually refer to a range vs another based on certain conditions. For ex,
IF($A$6:$A=XX,'DATA'!$N:$N,'DATA'!$M:$M).
The formula then breaks if I do this
=ARRAYFORMULA(SUMIF(IF($A$6:$A=XX,'DATA'!$N:$N,'DATA'!$M:$M)&'DATA'!$U:$U&'DATA'!$V:$V&'DATA'!$BV:$BV,$B$6:$B&$E$30&$D$26&$C$36,'DATA'!$AG:$AG).
Any way/workaround to make it work? I tried multiple times, read multiple forums but can't find an answer.
if XX
is not named range it should be in quotes
try:
=ARRAYFORMULA(SUM(IF(
IF(A6="XX", 'DATA'!N:N, 'DATA'!M:M)&DATA!U:U&DATA!V:V&DATA!BV:BV=B6:B&E30&D26&C36,
DATA!AG:AG, )))
=ARRAYFORMULA(IFNA(VLOOKUP(B7:B&C7:C,
QUERY({DATA!A2:A&DATA!A1, DATA!C2:F; DATA!B2:B&DATA!B1, DATA!C2:F},
"select Col1,sum(Col5)
where Col2 = '"&D4&"'
and Col4 = '"&D3&"'
group by Col1
label sum(Col5)''"), 2, 0)))