More Sumproduct!

[Guest]

I have the following formula in a bit of VBA, which works fine:
"=SUMPRODUCT(('0'!$E$2:$E$5000={""Tranche 4"",""Tranche 4 - R0"",""T4
Prem"",""T4 BNL"",""T4 DS"",""T4 SR""})*('0'!$F$2:$F$5000=$B28))"

However, it's a bit unwieldy and I'd like to introduce wildcards so it would
ideally look more like this and thus better accomodate future changes. I soon
discovered that wildcards don't work in this formula!
"=SUMPRODUCT(('0'!$E$2:$E$5000={""Tranche
4*"",""T4*""})*('0'!$F$2:$F$5000=$B28))"

I've had a good look at the many posts on this topic and have tried some of
the solutions but cannot get the right answers. I'd appreciate any
suggestions you may have!

Many thanks

 

[Bob Phillips]

I have done it in Excel, I will leave it to you to adapt back to VBA

=SUMPRODUCT(((LEFT('0'!$E$2:$E$5000,9)="Tranche
4")+(LEFT('0'!$E$2:$E$5000,2)="T4"))*('0'!$F$2:$F$5000=$B28))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Works brilliantly, thank you for your help.

I have done it in Excel, I will leave it to you to adapt back to VBA

=SUMPRODUCT(((LEFT('0'!$E$2:$E$5000,9)="Tranche
4")+(LEFT('0'!$E$2:$E$5000,2)="T4"))*('0'!$F$2:$F$5000=$B28))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)