moneytastesbad
Board Regular
- Joined
- Aug 28, 2006
- Messages
- 106
I have searched the board multiple times, but cant find the solution to my problem, which is.....
I have a rather large financial worksheet that I am trying to extract some specific data from.
The sheet contains budget and expense #'s for the fiscal year for several projects. The project names are listed in column G and the same project can be on multiple lines, depending on what the expense is. Column J contains either "Cap" for Capital, or "Exp" for Expense. The monthly data starts at Row L. Each month has 6 columns, the one that matters for this scenereo is the 5th one for each month, which is the Forecast Column.
Our Fiscal Year is April 1 - March 31, but this sheet ruins from March - March so that we can carry over anything from the previous year. So the forecast column for March is column "P", April is column "V" and so on, adding 6 columns for each month.......
With me so far?
What I am trying to do is get the total forecasted expenses amount for a specific project for the remainder of the fiscal year.
In other words I need to look for a specific Project Code in Row G, look for "Exp" in Row J, Then get only the numbers that are in the Forecasted Column for months in the future and add them all up.
Just in case that is not totaly clear----
criteria to meet
Column G = "Project Code"
Column J = "Exp"
Row 2 > this month
Row 3 = Forecast.
I hope this makes sense.
________________________________________________
I have tried using SUMPRODUCT, but it is returning a #VALUE error.
It works fine if I want to total just one month, but when I add in multiple future months it stops working.
Here is what I used
Here is the code I used to total only the forecast for April
Any suggestions?
Thanks,
Travis
I have a rather large financial worksheet that I am trying to extract some specific data from.
The sheet contains budget and expense #'s for the fiscal year for several projects. The project names are listed in column G and the same project can be on multiple lines, depending on what the expense is. Column J contains either "Cap" for Capital, or "Exp" for Expense. The monthly data starts at Row L. Each month has 6 columns, the one that matters for this scenereo is the 5th one for each month, which is the Forecast Column.
Our Fiscal Year is April 1 - March 31, but this sheet ruins from March - March so that we can carry over anything from the previous year. So the forecast column for March is column "P", April is column "V" and so on, adding 6 columns for each month.......
With me so far?
What I am trying to do is get the total forecasted expenses amount for a specific project for the remainder of the fiscal year.
In other words I need to look for a specific Project Code in Row G, look for "Exp" in Row J, Then get only the numbers that are in the Forecasted Column for months in the future and add them all up.
Just in case that is not totaly clear----
criteria to meet
Column G = "Project Code"
Column J = "Exp"
Row 2 > this month
Row 3 = Forecast.
I hope this makes sense.
________________________________________________
I have tried using SUMPRODUCT, but it is returning a #VALUE error.
It works fine if I want to total just one month, but when I add in multiple future months it stops working.
Here is what I used
Code:
=SUMPRODUCT(--(ISNUMBER(SEARCH("SAPHR",$G$4:$G$352))),($J$4:$J$352="Exp"),--($L$2:$CK$2>TODAY()),--($L$3:$CK$3="Forecast"),L4:CK352)
Here is the code I used to total only the forecast for April
Code:
=SUMPRODUCT(--(ISNUMBER(SEARCH("SAPHR",$G$4:$G$352))),--($J$4:$J$352="Exp"),$V$4:$V$352)
Any suggestions?
Thanks,
Travis