Can I use wildcard characters in array formulas

D

devobrown

I am using Excel 2007 and I am trying to use an array formula to search based
on two criteria: the year, and an account number. The array contains account
numbers in the format #.###.## and #.###.##.#. I want to sum only amounts
where the account number matches the first format and exclude those that
match the second format. For example, I may have account number 1.111.1,
which I would include, but account number 1.111.1.1 I would not. The array
fomula I have created,
{=SUM(IF((YEAR($B$2:$B$5294)=G$1)*($A$2:$A$5294="*.*.*"),$D$2:$D$5294,0))}
treats all results of the test for the account number as if they are false
and returns 0, even though I know that there are true results in the array.
As I have used this logic many times in the past, the only problem I could
see is the use of the wildcard characters.
 
T

T. Valko

I'm assuming the account numbers are TEXT entries. There may be a better way
but this works:

=SUMPRODUCT(--(LEN(A2:A10)-LEN(SUBSTITUTE(A2:A10,".",""))=2),--(YEAR(B2:B10)=G1),D2:D10)

The criteria is include entries with 2 dots.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top