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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
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,253
Messages
5,546,774
Members
410,757
Latest member
jonni
Top