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.

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.

--
Biff
Microsoft Excel MVP


"devobrown" wrote in message
...
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.