Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Wildcard characters in countif array formulas
anyone know how to get wildcards to work in a countif array formula? Here's
what I"m working with: {=COUNT(IF((Sales!$G$2:$G$969="ax260*")*(Sales!$J$ 2:$J$969='Rep Summary'!E5),Sales!B2:B969))} If I enter a specific value (R600 for example) in the first argument, I get a solid result. budt adding the wildcard (AX260*) causes a 0 result. any ideas are greatly appreciated. -- Greg |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Wildcard characters in countif array formulas
Try this, normally entered:
=SUMPRODUCT(--(LEFT(Sales!$G$2:$G$969,5)="ax260"),--(Sales!$J$2:$J$969='Rep Summary'!E5),--(ISNUMBER(Sales!B2:B969))) -- Biff Microsoft Excel MVP "gregtal" wrote in message ... anyone know how to get wildcards to work in a countif array formula? Here's what I"m working with: {=COUNT(IF((Sales!$G$2:$G$969="ax260*")*(Sales!$J$ 2:$J$969='Rep Summary'!E5),Sales!B2:B969))} If I enter a specific value (R600 for example) in the first argument, I get a solid result. budt adding the wildcard (AX260*) causes a 0 result. any ideas are greatly appreciated. -- Greg |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Wildcard characters in countif array formulas
I'll try it. I should say that not all "root" values being sought are the
same length, but I might be able to find a length that will work in all instances. Thanks again. -- Greg "T. Valko" wrote: Try this, normally entered: =SUMPRODUCT(--(LEFT(Sales!$G$2:$G$969,5)="ax260"),--(Sales!$J$2:$J$969='Rep Summary'!E5),--(ISNUMBER(Sales!B2:B969))) -- Biff Microsoft Excel MVP "gregtal" wrote in message ... anyone know how to get wildcards to work in a countif array formula? Here's what I"m working with: {=COUNT(IF((Sales!$G$2:$G$969="ax260*")*(Sales!$J$ 2:$J$969='Rep Summary'!E5),Sales!B2:B969))} If I enter a specific value (R600 for example) in the first argument, I get a solid result. budt adding the wildcard (AX260*) causes a 0 result. any ideas are greatly appreciated. -- Greg |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Wildcard characters in countif array formulas
Thanks again. It did work. I'd still like to figure out how to make the
wildcard work, just becase it would save creating many variations of the same formula, but otherwise, it workd fine. thanks for your prompt reply. -- Greg "T. Valko" wrote: Try this, normally entered: =SUMPRODUCT(--(LEFT(Sales!$G$2:$G$969,5)="ax260"),--(Sales!$J$2:$J$969='Rep Summary'!E5),--(ISNUMBER(Sales!B2:B969))) -- Biff Microsoft Excel MVP "gregtal" wrote in message ... anyone know how to get wildcards to work in a countif array formula? Here's what I"m working with: {=COUNT(IF((Sales!$G$2:$G$969="ax260*")*(Sales!$J$ 2:$J$969='Rep Summary'!E5),Sales!B2:B969))} If I enter a specific value (R600 for example) in the first argument, I get a solid result. budt adding the wildcard (AX260*) causes a 0 result. any ideas are greatly appreciated. -- Greg |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Wildcard characters in countif array formulas
Replace:
--(LEFT(Sales!$G$2:$G$969,5)="ax260") With: --(ISNUMBER(SEARCH("ax260",Sales!$G$2:$G$969))) Better to use a cell to hold the criteria: A1 = ax260 --(ISNUMBER(SEARCH(A1,Sales!$G$2:$G$969))) Note that this will find the substring *anywhere* within the string: ax260yyy yyyax260 yyax260yy -- Biff Microsoft Excel MVP "gregtal" wrote in message ... Thanks again. It did work. I'd still like to figure out how to make the wildcard work, just becase it would save creating many variations of the same formula, but otherwise, it workd fine. thanks for your prompt reply. -- Greg "T. Valko" wrote: Try this, normally entered: =SUMPRODUCT(--(LEFT(Sales!$G$2:$G$969,5)="ax260"),--(Sales!$J$2:$J$969='Rep Summary'!E5),--(ISNUMBER(Sales!B2:B969))) -- Biff Microsoft Excel MVP "gregtal" wrote in message ... anyone know how to get wildcards to work in a countif array formula? Here's what I"m working with: {=COUNT(IF((Sales!$G$2:$G$969="ax260*")*(Sales!$J$ 2:$J$969='Rep Summary'!E5),Sales!B2:B969))} If I enter a specific value (R600 for example) in the first argument, I get a solid result. budt adding the wildcard (AX260*) causes a 0 result. any ideas are greatly appreciated. -- Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I use wildcard characters in array formulas | Excel Worksheet Functions | |||
wildcard characters | Excel Discussion (Misc queries) | |||
Need help on countif and sumif function with dates and wildcard characters | Excel Discussion (Misc queries) | |||
WildCard Characters | Excel Worksheet Functions | |||
Using wild card characters in array formulas | Excel Worksheet Functions |