G
Guest
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.
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.
10)