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.
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: