Rectify function - combination of Sumproduct and OR
 

Hi,

1) how can I combine Sumproduct and the OR function?
eg

av Bank1 1
av Bank2 2
av Bank3 3
av LADD 4
av LCH 3
av MSL 2


=SUMPRODUCT(($B$19:$B$24="av")*OR(($C$19:$C$24<"B ank1"),($C$19:$C$24<"Bank
2"),($C$19:$C$24<"Bank3"))*($D$19:$D$24))
returns 15


but I want it to pick up only LADD, LCH & MSL
i.e return 9!



2) can one use wildcard characters?
eg if there is a Bank4 and Bank5 in the list

Rectify function - combination of Sumproduct and OR
 

Try it like this:

=SUMPRODUCT(($B$19:$B$24="av")*(LEFT($C$19:$C$24,4 )<"Bank")*($D$19:$D
$24))

Hope this helps.

Pete

On Feb 27, 10:04*am, "Hilton" wrote:
Hi,

1) how can I combine Sumproduct and the OR function?
eg

* * * av Bank1 1
* * * av Bank2 2
* * * av Bank3 3
* * * av LADD 4
* * * av LCH 3
* * * av MSL 2

=SUMPRODUCT(($B$19:$B$24="av")*OR(($C$19:$C$24<"B ank1"),($C$19:$C$24<"Ban*k
2"),($C$19:$C$24<"Bank3"))*($D$19:$D$24))
returns 15

but I want it to pick up only LADD, LCH & MSL
i.e return 9!

2) can one use wildcard characters?
eg if *there is a Bank4 and Bank5 in the list

Rectify function - combination of Sumproduct and OR
 

Hilton

Try:

=SUMPRODUCT(($B$19:$B$24="av")*($C$19:$C$24<"Bank 1")*($C$19:$C$24<"Bank2")*($C$19:$C$24<"Bank3"), ($D$19:$D$24))

Not sure about wildcards - I'll let somebody else respond on that.

Terry

"Hilton" wrote in message
...
Hi,

1) how can I combine Sumproduct and the OR function?
eg

av Bank1 1
av Bank2 2
av Bank3 3
av LADD 4
av LCH 3
av MSL 2


=SUMPRODUCT(($B$19:$B$24="av")*OR(($C$19:$C$24<"B ank1"),($C$19:$C$24<"Bank
2"),($C$19:$C$24<"Bank3"))*($D$19:$D$24))
returns 15


but I want it to pick up only LADD, LCH & MSL
i.e return 9!



2) can one use wildcard characters?
eg if there is a Bank4 and Bank5 in the list

Rectify function - combination of Sumproduct and OR
 

Hello,

I suggest
=SUMPRODUCT(--($B$19:$B$24="av"),SIGN(($C$19:$C$24="LADD") + ($C$19:$C
$24="LCH") + ($C$19:$C$24="MSL")),$D$19:$D$24)

Regards,
Bernd