Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
SUMPRODUCT | Excel Worksheet Functions | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct help | Excel Worksheet Functions |