SumProduct amendment

Ziggy12

Active Member
Joined
Jun 26, 2002
Messages
361
Hi
How do I modify the formula below to pick up instances where LEFT($I$9:$I$992,6) can also = 900024

=ROUND(-SUMPRODUCT((LEFT($I$9:$I$992,6)="900030")*1,$M$9:$M$992)*1.15,2)

cheers
Ziggy
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
Hi Ziggy

For ex.:

=ROUND(-SUMPRODUCT((LEFT($I$9:$I$19,6)={"900030","900024"})*$M$9:$M$19)*1.15,2)

=ROUND(-SUMPRODUCT(ISNUMBER(MATCH(LEFT($I$9:$I$992,6),{"900030","900024"},0))*1,$M$9:$M$992)*1.15,2)
 

Ziggy12

Active Member
Joined
Jun 26, 2002
Messages
361
Thanks. Didn't know {} could be used outside of array formula.

ziggy
 

Watch MrExcel Video

Forum statistics

Threads
1,114,418
Messages
5,547,812
Members
410,813
Latest member
Vhinzvirgo
Top