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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

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,337
Messages
5,547,349
Members
410,785
Latest member
phillippaige
Top