Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How rectify the DATA Error [47 OF 94 33 F4 55] | Excel Discussion (Misc queries) | |||
SUMPRODUCT, SUM(IF(...), or some combination? | Excel Worksheet Functions | |||
i cannot use the mouse right click in excel. how to rectify? | New Users to Excel | |||
Help with Combination Function | Excel Worksheet Functions | |||
query wizard has stopped working how do i rectify this | Excel Worksheet Functions |