I have this extemely large formula that is working fine, I want to define it
using the { } or funcunatily.

The current formula reads as:

=SUMPRODUCT(('Warehouse Management'!$F$15:$F$24="Apple")*('Warehouse
Management'!$D$15:$D$24<"FILL")*('Warehouse
Management'!$D$15:$D$24<"fill")*('Warehouse
Management'!$D$15:$D$24<"Fill")*('Warehouse
Management'!$D$15:$D$24<"")*('Warehouse Management'!$C$15:$C$24))

I tried but faild the following:

=SUMPRODUCT(('Warehouse Management'!$F$15:$F$24="Apple")*('Warehouse
Management'!$D$15:$D$24<{"FILL","fill","Fill",""} )*('Warehouse
Management'!$C$15:$C$24))

This doesnt seem to work, any ideas?

Thanks

Sean,

SUMPRODUCT doesn't work that way. You need to have one dimensional arrays only. Also, < is not
case sensitive, so you don't need to check for FILL & fill & Fill. If it was case sensitive, then
you could use something like

(UPPER('Warehouse Management'!$D$15:$D$24)<"FILL")

for the three Fill cases.... but, of course, there isn't any need for it...

HTH,
Bernie
MS Excel MVP


"Sean" wrote in message
...
I have this extemely large formula that is working fine, I want to define it
using the { } or funcunatily.

The current formula reads as:

=SUMPRODUCT(('Warehouse Management'!$F$15:$F$24="Apple")*('Warehouse
Management'!$D$15:$D$24<"FILL")*('Warehouse
Management'!$D$15:$D$24<"fill")*('Warehouse
Management'!$D$15:$D$24<"Fill")*('Warehouse
Management'!$D$15:$D$24<"")*('Warehouse Management'!$C$15:$C$24))

I tried but faild the following:

=SUMPRODUCT(('Warehouse Management'!$F$15:$F$24="Apple")*('Warehouse
Management'!$D$15:$D$24<{"FILL","fill","Fill",""} )*('Warehouse
Management'!$C$15:$C$24))

This doesnt seem to work, any ideas?

Thanks

Why not just use

=SUMPRODUCT(('Warehouse Management'!$F$15:$F$24="Apple")*
('Warehouse Management'!$D$15:$D$24<"Fill")*
('Warehouse Management'!$D$15:$D$24<"")*
('Warehouse Management'!$C$15:$C$24))

it is not case-sensitive

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Sean" wrote in message
...
I have this extemely large formula that is working fine, I want to define
it
using the { } or funcunatily.

The current formula reads as:

=SUMPRODUCT(('Warehouse Management'!$F$15:$F$24="Apple")*('Warehouse
Management'!$D$15:$D$24<"FILL")*('Warehouse
Management'!$D$15:$D$24<"fill")*('Warehouse
Management'!$D$15:$D$24<"Fill")*('Warehouse
Management'!$D$15:$D$24<"")*('Warehouse Management'!$C$15:$C$24))

I tried but faild the following:

=SUMPRODUCT(('Warehouse Management'!$F$15:$F$24="Apple")*('Warehouse
Management'!$D$15:$D$24<{"FILL","fill","Fill",""} )*('Warehouse
Management'!$C$15:$C$24))

This doesnt seem to work, any ideas?

Thanks