Sumproduct WildCard - A Tough one

[andibevan]

Hi All,

I have been trying to use the following formula to count the number o
items with RSK in column A and also *containing* ACT in column E. (i.e
they match both criteria

sumproduct((a5:a508)="RSK")*(E5:E508)="*ACT*"))

I have now been informed that "*" does not work as a wildcard within a
array (it does work as a wild card in other forumlas such as countif.

My questions are:-

1) is there a wild card that can be used with a sumproduct array?
2) if not, is there another way that I can count the number of items a
specified above?

Any help would be gladly received

Thanks

And

 

[nh786]

Adding"--" does the trick as follows

sumproduct(--(a5:a508)="RSK"),--(E5:E508)="*ACT*")

 

[andibevan]

What does "--" do / how does it work?

Thanks

And

 

[nh786]

A "--" coerces sumproduct to evaluate an array

A single "-" can also be used but may give negative value unless use
in pairs ie. if odd number of conditions are to be evaluated

 

[Daniel.M]

Hi,

SEARCH() could use * and ? metacharacters but, in your case, you don't even need
it.

Try:
=SUMPRODUCT((A5:A508="RSK")*ISNUMBER(SEARCH("ACT",E5:E508)))

Regards,

Daniel M.

 

[Aladin Akyurek]

Coercion using -- has no bearing on the OP's question. See Daniel's
response.

 

[GerryK]

FYI from a previous post:
the --s are necessary since the individual terms return
arrays of booleans, and SUMPRODUCT() errors on booleans.
The double unary minus converts them into numbers. One
could instead multiply the arrays but the comma form is
about 25% faster.
HTH