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.

 

[Joe4]

Have you read through this question. you always seem to at least be able to point me in the right direction for a solution.

I tend to be a very visual person (I have a hard time picturing complicated or involved setups from just a written explanation).
So, I would be happy to take a look if I could see a visual image of how your data is structured, and what your expected output would look like.
(Just note that sometimes people try uploading files to a file sharing site, and I cannot download those from my work location).

 

[AliGW]

Joe is not alone: I much prefer to see some sample data rather than having to reconstruct what is being described from scratch.

 

[Joe4]

Yes, another issue with trying to reconstruct it is that sometimes we make assumptions that do not accurately reflect your exact set-up.

Personally, I am unable to use those programs myself (my work has a very stringent policy when it comes to those things). I have resorted to using Code Tags (since it maintains spacing) and manually create an image. I keep going back-and-forth with "Preview Post" (in the Advanced Reply menu) to make sure it is readable before posting. It isn't the prettiest thing, but often gets the job done, i.e.

Name    Gender    Age
Adam       M      31
Barb       F      27
Carl       M      44

 

[RCBricker]

Widget	Line Type	Jan	Feb	Mar
125	Phase	A	A	A
125	CostX	5000	5000	5000
400	Phase	A	A	B
400	CostX	12000	12000	12000
Line Type	Phase	Jan	Feb	Mar
CostX	A	17000	17000	5000
CostX	B	-	-	12000

<tbody>
</tbody><colgroup><col><col span="4"></colgroup>

so my basic issue is that the designator "A" is in the rows. The formula needs to find each instance of "A" and add the offsetting cells together (per widget). There are a number of widgets and there are actually two phases (Cost and Revenue) all are controlled by a designator. There are three designators ( "A", "B" and "R"). So we want to sum all widgets that are A, all that are B and then all that are R for Cost and then each of the three for all widgets for Revenue.

Only A and B are shown here to simplify the matter at hand.

 

[Joe4]

OK. In your example, assume that all that is posted in ranges B4:F12.
So your totals are in cells D11:F12.
Copy this formula in cell D11 and copy/paste to the other 5 totals calculations:

=SUMIFS(D$6:D$9,$C$6:$C$9,$B11,D$5:D$8,$C11)

This returned the totals your example showed for me.

Note the placement of the absolute range references ($) is critical in these formulas.

 

[RCBricker]

OK. In your example, assume that all that is posted in ranges B4:F12.
So your totals are in cells D11:F12.
Copy this formula in cell D11 and copy/paste to the other 5 totals calculations:

=SUMIFS(D$6:D$9,$C$6:$C$9,$B11,D$5:D$8,$C11)

This returned the totals your example showed for me.

Note the placement of the absolute range references ($) is critical in these formulas.

****. I thought of sumifs but I never thought of offsetting the ranges when it was looking at total and for "A".

 

[RCBricker]

****. I thought of sumifs but I never thought of offsetting the ranges when it was looking at total and for "A".

Ok when I apply this to a larger table it is not quite there.

Widget	Line Type	Jan	Feb	Mar
125	Phase	A	A	A
125	CostX	1200	1200	1200
125	CostY	2500	2500	2500
125	CostZ	500	500	500
400	Phase	A	A	A
400	CostX	12000	12000	12000
400	CostY	7500	7500	7500
400	CostZ	0	0	0
655	Phase	A	A	A
655	CostX	2250	2250	2250
655	CostY	250	250	250
780	Phase	A	A	A
780	CostY	4100	4100	4100
780	CostZ	6000	6000	6000

<tbody>
</tbody><colgroup><col span="2"><col span="3"></colgroup>

I should get this:

Line Type	Phase	Jan	Feb	Mar
CostX	A	15450	15450	15450
CostX	B	-	-	-
CostX	C	-	-	-
CostY	A	14350	14350	14350
CostY	B	-	-	-
Costy	C	-	-	-
CostZ	A	6500	6500	6500
CostZ	B	-	-	-
CostZ	C	-	-	-

<tbody>
</tbody><colgroup><col span="2"><col span="3"></colgroup>

But this is what the formula gives me:

Line Type	Phase	Jan	Feb	Mar
CostX	A	15450	15450	15450
CostX	B	0	0	0
CostX	C	0	0	0
CostY	A	4100	4100	4100
CostY	B	0	0	0
Costy	C	0	0	0
CostZ	A	0	0	0
CostZ	B	0	0	0
CostZ	C	0	0	0

<tbody>
</tbody><colgroup><col span="2"><col span="3"></colgroup>

 

[Joe4]

Therein lies the danger of oversimplifying your example - you might get a solution that won't work for your actual structure because assumptions are used that don't reflect your real data. One piece of advice I often give people is do NOT oversimplify your data to the point where it no longer reflects the actual structure of your data, because this is what can happen.

My formula was based on the sample set of data you posted, in which there is an alternating pattern of Phase rows and Cost rows (so we could always look at the row above any Cost number to see what Phase it is to be applied to). However, in the second example you posted, there are now instances in which there are multiple Cost rows in a row, and it isn't always the same number (sometimes 2, sometimes 3).

The truth of the matter is that this structure makes it very hard to work with, and unfortunately I do not have a good solution for you.

 

[RCBricker]

Therein lies the danger of oversimplifying your example - you might get a solution that won't work for your actual structure because assumptions are used that don't reflect your real data. One piece of advice I often give people is do NOT oversimplify your data to the point where it no longer reflects the actual structure of your data, because this is what can happen.

My formula was based on the sample set of data you posted, in which there is an alternating pattern of Phase rows and Cost rows (so we could always look at the row above any Cost number to see what Phase it is to be applied to). However, in the second example you posted, there are now instances in which there are multiple Cost rows in a row, and it isn't always the same number (sometimes 2, sometimes 3).

The truth of the matter is that this structure makes it very hard to work with, and unfortunately I do not have a good solution for you.

When you posted the sumifs I was excited. However, I was worried that more data might change it as it did. Well back to the drawing board. Thanks for trying.

*edit

What if it always had the same structure? So every widget would have Costx, costy and costz even if the answer was zero?

 

[Joe4]

What if it always had the same structure? So every widget would have Costx, costy and costz even if the answer was zero?

Yes, we could work with that. We would just have three different sets of formulas, one for the CostX records, one for the CostY records, and one for the CostZ records.
So, if your data was in B4:F20, and the totals were in cells D23:F31, here are the formulas:

For CostX records (enter in cell D23 and copy to D23:F25):

=SUMIFS(D$6:D$18,$C$6:$C$18,$B23,D$5:D$17,$C23)

For CostY records (enter in cell D26 and copy to D26:F28):

=SUMIFS(D$7:D$19,$C$7:$C$19,$B26,D$5:D$17,$C26)

For CostZ records (enter in cell D29 and copy to D29:F31):

=SUMIFS(D$8:D$20,$C$8:$C$20,$B29,D$5:D$17,$C29)