SUMPRODUCT Question?

[gtbb]

Hi All,

Is there an alternative way to shorten the below formula?

Thanks a lot!

SUMPRODUCT(--('All Transactions'!$A$2:$A$3000=A9),--('All Transactions'!$C$2:$C$3000="INTEREST"),'All Transactions'!$B$2:$B$3000)+SUMPRODUCT(--('All Transactions'!$A$2:$A$3000=A9),--('All Transactions'!$C$2:$C$3000="INTEREST ADJUSTMENT"),'All Transactions'!$B$2:$B$3000)+SUMPRODUCT(--('All Transactions'!$A$2:$A$3000=A9),--('All Transactions'!$C$2:$C$3000="INTEREST ADJUSTMENTS"),'All Transactions'!$B$2:$B$3000)

 

[Domski]

Maybe:

=SUMPRODUCT(--('All Transactions'!$A$2:$A$3000=A9),--(LEFT('All Transactions'!$C$2:$C$3000,8)="INTEREST"),'All Transactions'!$B$2:$B$3000)

Dom

 

[pgc01]

Hi gtbb

Domski solution will work for any string that starts with "INTEREST". If you want to allow only the exact strings in your post:

=SUMPRODUCT(--('All Transactions'!$A$2:$A$3000=A9),--ISNUMBER(MATCH('All Transactions'!$C$2:$C$3000,{"INTEREST","INTEREST ADJUSTMENT","INTEREST ADJUSTMENTS"},0)),'All Transactions'!$B$2:$B$3000)

 

[gtbb]

Wow! They both work so great!!

A big thanks to Domski and pcg01!!!!!!