More Sumproduct!
 

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

More Sumproduct!
 

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)



"Ladymuck" wrote in message
...
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

More Sumproduct!
 

Works brilliantly, thank you for your help.

"Bob Phillips" wrote:

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)



"Ladymuck" wrote in message
...
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