# SumProduct amendment

#### Ziggy12

##### Active Member
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

### 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
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
Thanks. Didn't know {} could be used outside of array formula.

ziggy

#### pgc01

##### MrExcel MVP
You're welcome. Thanks for the feedback.

Replies
4
Views
69
Replies
1
Views
50
Replies
4
Views
67
Replies
2
Views
174
Replies
15
Views
114