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 Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,851
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,109,454
Messages
5,528,862
Members
409,841
Latest member
Orval_Effertz
Top