Indirect formula syntax

[erniepoe]

Hi all,

So I've got the following indirect formula, trying to sumproduct all the instances of Admin, Marketing, Implementation, Direct Install, and Incentives in column B and then sum column K,

The formula works if I only choose one of the above, but not for all 5, so I think I'm close to where I should be, but as it stands, I always get zero values when I search for all 5.

Anybody see where I've gone wrong?

Thanks for any help,
Ernie




(SUMPRODUCT((INDIRECT(Controls!$D$55&"!A2:A430")=$A6)*(INDIRECT(Controls!$D$55&"!K2:K430")*(INDIRECT(Controls!$D$55&"!B2:B430")="Admin")*(INDIRECT(Controls!$D$55&"!B2:B430")="Marketing")*(INDIRECT(Controls!$D$55&"!B2:B430")="Incentives")*(INDIRECT(Controls!$D$55&"!B2:B430")="Implementation")*(INDIRECT(Controls!$D$55&"!B2:B430")="Direct Install"))))))

 

[erniepoe]

No ideas as to what might be wrong?

 

[T. Valko]

No ideas as to what might be wrong?

Try it like this...

Use cells to hold the criteria.

A1:A5 =
Admin
Marketing
Implementation
Direct Install
Incentives

=SUMPRODUCT(--(INDIRECT(Controls!$D$55&"!A2:A430")=$A6),--(ISNUMBER(MATCH(INDIRECT(Controls!$D$55&"!B2:B430"),A1:A5,0))),INDIRECT(Controls!$D$55&"!K2:K430"))

 

[erniepoe]

that worked great, thanks!

 

[T. Valko]

that worked great, thanks!

You're welcome. Thanks for the feedback!