using function sumif
Col A currently contains only 2006 dates, but as soon as the new year rolls,
the same column will also contain 2007 dates. Col B contains numbers that I want to sum, depending on the date in column A. From now until Janualry 1st, I want to sum only the data containing 2006 dates. On January 1st, I want the sum to include 2007 entries as well. Some entries are entered in advance of the actual date. For instance, an entry could be made today with a January 2nd date. I wouldn't want to include that data in my sum until January actually rolls around. How do I write the formula? BTW, I'm not a Visual Basic programmer, I can only use formulas on the worksheet. Thanks in advance for any help. |
using function sumif
Hi Jan
You should be able to so this with SUMIFs: =SUMIF(A:A,"<01/01/2007",B:B) for all the 2006 data. It becomes a simple sum in 2007. Hope this helps! Richard Jan Buckley wrote: Col A currently contains only 2006 dates, but as soon as the new year rolls, the same column will also contain 2007 dates. Col B contains numbers that I want to sum, depending on the date in column A. From now until Janualry 1st, I want to sum only the data containing 2006 dates. On January 1st, I want the sum to include 2007 entries as well. Some entries are entered in advance of the actual date. For instance, an entry could be made today with a January 2nd date. I wouldn't want to include that data in my sum until January actually rolls around. How do I write the formula? BTW, I'm not a Visual Basic programmer, I can only use formulas on the worksheet. Thanks in advance for any help. |
using function sumif
One way where col D has the values and cell b1 has 1/1/2007
=SUMPRODUCT(--(YEAR(A8:A22)=2006),D8:D22)+if(today()b1,=SUMPROD UCT(--(YEAR(A8:A22)=2007),D8:D22),0) -- Don Guillett SalesAid Software "Jan Buckley" wrote in message ... Col A currently contains only 2006 dates, but as soon as the new year rolls, the same column will also contain 2007 dates. Col B contains numbers that I want to sum, depending on the date in column A. From now until Janualry 1st, I want to sum only the data containing 2006 dates. On January 1st, I want the sum to include 2007 entries as well. Some entries are entered in advance of the actual date. For instance, an entry could be made today with a January 2nd date. I wouldn't want to include that data in my sum until January actually rolls around. How do I write the formula? BTW, I'm not a Visual Basic programmer, I can only use formulas on the worksheet. Thanks in advance for any help. |
All times are GMT +1. The time now is 10:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com