Sum using multiple criteria in both rows and columns

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

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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Watch MrExcel Video

Forum statistics

Threads
1,114,676
Messages
5,549,374
Members
410,911
Latest member
AniEx
Top