Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Wildcards in Array Formulas
I've been struggling all day to go get a good COUNTIF with multiple criteria
going, and I think I might be closing in on a solution. What I've been doing now is using {=SUM((NamedRange1="Text1")*(NamedRange2="Text2")) } This way I've been able to get a count of how many rows there are where I find both cells containing "Text1" and cells containing "Text2". There are four cells containing "Text1" and two cells containing "Text2" but only one place where they intersect, meaning that the formular returns "2". So far so good. However, it is absolutely necessary that I be able to use wildcards in this, as I can only identify the first two characters of "Text2". That means that I want to, ideally, use: {=SUM((NamedRange1="Text1")*(NamedRange2="Te*"))} This returns 0 when it should return 1. I've tested this by using: {=SUM((NamedRange2="Text2")*1) versus {=SUM((NamedRange2="Te*")*1) The first one returns "1" whereas the second returns "0." This is incredibly frustrating - if someone has the answer to this I'd be very grateful. If you don't, maybe you can show me a better way of accomplishing what I want to do here? Thanks a lot. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Wildcards in Array Formulas
One way:
=SUMPRODUCT(--(LEFT(NamedRange1,5)="Text1"), --(LEFT(NamedRange2,2)="Te")) This is a bit more efficient than using the {=SUM(a * b)} array formula. See http://www.mcgimpsey.com/excel/doubleneg.html for mroe. In article , Babymech wrote: I've been struggling all day to go get a good COUNTIF with multiple criteria going, and I think I might be closing in on a solution. What I've been doing now is using {=SUM((NamedRange1="Text1")*(NamedRange2="Text2")) } This way I've been able to get a count of how many rows there are where I find both cells containing "Text1" and cells containing "Text2". There are four cells containing "Text1" and two cells containing "Text2" but only one place where they intersect, meaning that the formular returns "2". So far so good. However, it is absolutely necessary that I be able to use wildcards in this, as I can only identify the first two characters of "Text2". That means that I want to, ideally, use: {=SUM((NamedRange1="Text1")*(NamedRange2="Te*"))} This returns 0 when it should return 1. I've tested this by using: {=SUM((NamedRange2="Text2")*1) versus {=SUM((NamedRange2="Te*")*1) The first one returns "1" whereas the second returns "0." This is incredibly frustrating - if someone has the answer to this I'd be very grateful. If you don't, maybe you can show me a better way of accomplishing what I want to do here? Thanks a lot. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using wildcards in Array formulae | Excel Discussion (Misc queries) | |||
Wildcards in formulas? | Excel Worksheet Functions | |||
wildcards in formulas | Excel Discussion (Misc queries) | |||
Wildcards in Formulas | Excel Discussion (Misc queries) | |||
Wildcards in formulas? | Excel Worksheet Functions |