excel 2016 - SUMIFS with a criteria within a data set

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
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.
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
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).
 
Last edited:
Upvote 0

EssoExplJoe

New Member
Joined
Nov 11, 2016
Messages
24
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!
 
Upvote 0

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
How does one post a table. I want to post an example of what I need for this question, but do not know how.
 
Last edited:
Upvote 0

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
Upvote 0

Forum statistics

Threads
1,191,274
Messages
5,985,702
Members
439,974
Latest member
sjoerdbosch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top