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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

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,528
Messages
5,548,576
Members
410,852
Latest member
WernerS
Top