Wildcards in array formulas?

  • Thread starter Thread starter Montys Python {wink wink}
  • Start date Start date
M

Montys Python {wink wink}

Hello

I am trying to get a count of where item in column D begins with a 2
and the item in column F begins ma

Excel doesn't like the wild cards... all the numbers in column D begin
with a 2 (no exceptions) but are all different. Not everything in
column F starts with ma.

here is the formula that fails.
=SUM(IF(D2:D2808="2*",IF(F2:F2808="ma*",1,0)))

Any ideas?

cheers
 
Hi Frank

thanks for the faaaast response.

I tried that but it only returned a count of 2...I would expect a much
higher number.

Column D contains lots of blanks and lots of numbers that begin with a
2 eg.206698, 206687 etc.
Column F contains Product names..some of which begin 'Ma'

Would it help if column D was text and not number formatted? I
switched it around from one to the other and it didn't make a
difference.

thanks you are a lifesaver.
 
=SUMPRODUCT((LEFT(D2:D2808)="2")*((LEFT(F2:F2808,2)="ma")))

or

=SUMPRODUCT(--(LEFT(D1:D29)="2"),--((LEFT(F1:F29,2)="ma")))
 
You're welcome. Might be worth making a not of Frank's reply though, as this is
extremely useful when searching for a character(s) or number in 'any' part of
the cell, as opposed to limiting it to just the beginning or end etc.
 
Back
Top