Sumproduct with multiple criteria on the same column not working-CodePudding

I am writing a formula using SUMPRODUCT to show me the sum of a column with various criteria (i.e. multiple criteria in the same column) but I am getting 0 (zero) as a result, can anyone help me with this one? here is the formula:

=sumproduct(indirect($A10&"!E:E"),(indirect($A10&"!$F:$F")>=AA1)*(indirect($A10&"!$F:$F")<=AC1)*((indirect($A9&"!$D:$D")="Brand1") (indirect($A9&"!$D:$D")="Brand2") (indirect($A9&"!$D:$D")="Brand3") (indirect($A9&"!$D:$D")="Brand4")))

The first part of the formula works well, but everytime I put this: ((indirect($A9&"!$D:$D")="Brand1") (indirect($A9&"!$D:$D")="Brand2") (indirect($A9&"!$D:$D")="Brand3") (indirect($A9&"!$D:$D")="Brand4")) then the problem appearing giving me 0 (zero) results

CodePudding user response：

try the filter way:

=INDEX(SUM(FILTER(INDIRECT(A10&"!E:E"), 
                  INDIRECT(A10&"!F:F")>=AA1, 
                  INDIRECT(A10&"!F:F")<=AC1, 
       REGEXMATCH(INDIRECT(A10&"!D:D"), "Brand1|Brand2|Brand3|Brand4"))*1))