Problem with formula
Posted by Armand on January 30, 2002 5:53 AM
Hello,
I am managing budgets for three projects in Excel, and have to produce monthly expenditures reports...
My sheet is made of 5 columns of which Column A for Project #, Column B for month of payment (payments are made in local currency but converted in USD based on the rate applicable at that time) ....
I want a formula which will allow to have expenses made on each project separately and by month. For instance the datas are in A2:B300, I want to have all the expenses made for the project 6077 located in A2:A300 during the month of February 2001 (feb-02 is located in the cells between B2:B300
Can you help please ?
Thanks so much
Armand
Posted by Aladin Akyurek on January 30, 2002 6:49 AM
Armand --
Method 1. Use PivotTables.
Method 2. Do what follows.
I'll assume Project# to be in A, dates (like Jan-02 or 17-Jan-02, date-formatted) in B, and Expenditures in C (your desc isn't very clear about the lay-out).
In G1 enter: =MATCH(9.99999999999999E+307,B:B)
Make a list of 3-letter months in G from G2 on. That is: Jan, Feb, Mar, etc.
Enter Project# in row 1 from H1 on.
In H2 enter: =SUMPRODUCT((OFFSET($A$2,0,0,$G$1,1)=H$1)+0,(TEXT(OFFSET($B$2,0,0,$G$1,1),"mmm")=$G2)+0,(OFFSET($C$2,0,0,$G$1,1)))
Copy this across then down.
Aladin
============
