Does anyone know how I can reduce or clean up this formula? It works but it looks like a dogs breakfast.
Essentially I want 10 conditions to be met before summing a particular range i.e. Data!$F$4:$GC$48. All conditions are the same except the 4th condition which varies from = $A$6 to = $A$12, and I have just added an additional 6 Sumproducts, changing the 4th reference each time.
Is there a way to have the 4th condition read "$A$6 or $A$7 or $A$8" etc..?
=((SUMPRODUCT((Data!$C$4:$C$48=B$4)*(Data!$E$4:$E$48=$A34)*(Data!$F$2:$GC$2=B$3)*(Data!$F$3:$GC$3=$A$6),Data!$F$4:$GC$48)+SUMPRODUCT((Data!$C$4:$C$48=B$4)*(Data!$E$4:$E$48=$A34)*(Data!$F$2:$GC$2=B$3)*(Data!$F$3:$GC$3=$A$7),Data!$F$4:$GC$48)+SUMPRODUCT((Data!$C$4:$C$48=B$4)*(Data!$E$4:$E$48=$A34)*(Data!$F$2:$GC$2=B$3)*(Data!$F$3:$GC$3=$A$8),Data!$F$4:$GC$48)+SUMPRODUCT((Data!$C$4:$C$48=B$4)*(Data!$E$4:$E$48=$A34)*(Data!$F$2:$GC$2=B$3)*(Data!$F$3:$GC$3=$A$9),Data!$F$4:$GC$48)+SUMPRODUCT((Data!$C$4:$C$48=B$4)*(Data!$E$4:$E$48=$A34)*(Data!$F$2:$GC$2=B$3)*(Data!$F$3:$GC$3=$A$10),Data!$F$4:$GC$48)+SUMPRODUCT((Data!$C$4:$C$48=B$4)*(Data!$E$4:$E$48=$A34)*(Data!$F$2:$GC$2=B$3)*(Data!$F$3:$GC$3=$A$11),Data!$F$4:$GC$48)+SUMPRODUCT((Data!$C$4:$C$48=B$4)*(Data!$E$4:$E$48=$A34)*(Data!$F$2:$GC$2=B$3)*(Data!$F$3:$GC$3=$A$12),Data!$F$4:$GC$48))
Essentially I want 10 conditions to be met before summing a particular range i.e. Data!$F$4:$GC$48. All conditions are the same except the 4th condition which varies from = $A$6 to = $A$12, and I have just added an additional 6 Sumproducts, changing the 4th reference each time.
Is there a way to have the 4th condition read "$A$6 or $A$7 or $A$8" etc..?
=((SUMPRODUCT((Data!$C$4:$C$48=B$4)*(Data!$E$4:$E$48=$A34)*(Data!$F$2:$GC$2=B$3)*(Data!$F$3:$GC$3=$A$6),Data!$F$4:$GC$48)+SUMPRODUCT((Data!$C$4:$C$48=B$4)*(Data!$E$4:$E$48=$A34)*(Data!$F$2:$GC$2=B$3)*(Data!$F$3:$GC$3=$A$7),Data!$F$4:$GC$48)+SUMPRODUCT((Data!$C$4:$C$48=B$4)*(Data!$E$4:$E$48=$A34)*(Data!$F$2:$GC$2=B$3)*(Data!$F$3:$GC$3=$A$8),Data!$F$4:$GC$48)+SUMPRODUCT((Data!$C$4:$C$48=B$4)*(Data!$E$4:$E$48=$A34)*(Data!$F$2:$GC$2=B$3)*(Data!$F$3:$GC$3=$A$9),Data!$F$4:$GC$48)+SUMPRODUCT((Data!$C$4:$C$48=B$4)*(Data!$E$4:$E$48=$A34)*(Data!$F$2:$GC$2=B$3)*(Data!$F$3:$GC$3=$A$10),Data!$F$4:$GC$48)+SUMPRODUCT((Data!$C$4:$C$48=B$4)*(Data!$E$4:$E$48=$A34)*(Data!$F$2:$GC$2=B$3)*(Data!$F$3:$GC$3=$A$11),Data!$F$4:$GC$48)+SUMPRODUCT((Data!$C$4:$C$48=B$4)*(Data!$E$4:$E$48=$A34)*(Data!$F$2:$GC$2=B$3)*(Data!$F$3:$GC$3=$A$12),Data!$F$4:$GC$48))