Can I use wildcard characters in array formulas
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. |
Can I use wildcard characters in array formulas
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. |
All times are GMT +1. The time now is 06:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com