excel 2016 - SUMIFS with a criteria within a data set

[RCBricker]

I have a data set.

Here is the layout

Column B=Project ID
Column C=End date
Column D=Line type
COlumns E-* = months of the year(s)

I need to summarize this data below the data set.

example below uses Project ID 375

So each project ID will have three lines.

First line for our example would start on row 5, row four is our overall column headers.
Starting in B2 and moving right

B5-B7=375
C5-C7=*end date*
D5= "Stage" This will have three possible entries one for each month. (all projects have all three)

E5-G5 = "Funded" - this is the first of the three possible entries
H5-J5= "Unfunded" - This is the second of the three possible entries
K5 - K*= "Recompete" - This is the last of the three possible entries.
D6="Revenue" - One of two possible monetary designators (all projects have both)
D7="Cost" - second of two possible monetary designators (all projects have both)
E6- E*=various monetary values (These are the values for Revenue)
E7- E*=various monetary values (These are the values for Costs)

E8 - J8 = "Funded" - this is the first of the three possible entries
K8 - L8= "Unfunded" - This is the second of the three possible entries
M8 - M*= "Recompete" - This is the last of the three possible entries.
D9 ="Revenue" - One of two possible monetary designators (all projects have both)
D10 = "Cost" - second of two possible monetary designators (all projects have both)
E9 - E*=various monetary values (These are the values for Revenue)
E10 - E*=various monetary values (These are the values for Costs)

so in our example

E6 = 5000 (Revenue for project 375 in the month of Jan)
E7=2000 (Revenue for project 375 in the month of Jan)
E8= "Funded" (stage for the next project)
E9=12000 (Revenue for the next project in the month of Jan)
E10=3000 (Cost for the next project in the month of Jan)

The summary starts after the data set. The summary is all project revenues or cost added together and separated by whether it is funded, unfunded or recompete

In this example lets start on Row 15.

E15 starts the monthly headers.

C16-C18 = "revenue"
C20 - C22="Cost"

D16="Funded"
D17="Unfunded"
D18="Recompete"

D20="Funded"
D21="Unfunded"
D22="Recompete"

Starting in E16 and corresponding with the 6 rows directly above are where the summary of values take place. So not counting row 19 but E16 - *lastcolumn* row 22.

E16=17000 (total Revenue of Funded projects for Jan)
E17=0 (in this example since both projects are funded and as such no Unfunded)
E18 = 0 (in this example since both projects are funded and as such no Recompete)
E19 = is blank
E20 = 5000 (total Cost of Funded projects for Jan)
E21 = 0 (in this example since both projects are funded and as such no Unfunded)
E22 = 0 (in this example since both projects are funded and as such no Recompete)

TLDR:

I need a way to add values that match the following condition

Line type (column D)
Month (column Header)

last condition is the issue
Status (which is either funded, unfunded or recompete) which is found for each project within the data set itself.

 

[EssoExplJoe]

Maybe you should add a separate column for Status!

 

[RCBricker]

Maybe you should add a separate column for Status!

yeah. Unfortunately it is not my spreadsheet and the boss doesn't want to add more area to the data set. I made the same suggestion. Also shot down my second suggestion which was to add a revenue funded, revenue unfunded and revenue recompete to each project (as well as three lines for each status for cost).

 

[EssoExplJoe]

I know the drill. Maybe a UDF would work. Note if he was a REAL manager, he would want to see a summary page, not the nitty gritty detail!

 

[RCBricker]

How does one post a table. I want to post an example of what I need for this question, but do not know how.

 

[AliGW]

Grab the Excel add-in linked to in my signature line.

 

[RCBricker]

Grab the Excel add-in linked to in my signature line.

Nice website. Unfortunately work browser blocked the link to the addin

 

[AliGW]

Nice website. Unfortunately work browser blocked the link to the addin

Not my website or my add-in! The add-in is hosted on DropBox, unfortunately.

 

[Joe4]

There are also other tools mentioned in Section B here. Perhaps at least one of those other options is not blocked for you?
http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[RCBricker]

There are also other tools mentioned in Section B here. Perhaps at least one of those other options is not blocked for you?
http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

Hey Joe. thanks for the link. I will check it out. Have you read through this question. you always seem to at least be able to point me in the right direction for a solution.