SUMIFS using conditions multiple Criteria

[Akshath]

Hi All,

The desired output column, should be a cumulative figure of the area, the criteria being the summation should happen when the status is Completed & names are of the same tech.

Example:

SJR Tech = 100+200+300 = 600

If the status is Proposed or Under Construction, the output should be area as listed.

Example:

SJR tech 3 = 200 (since status is proposed)

Would really appreciate the help. Thanks

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Status[/TD]
[TD]Name[/TD]
[TD]Area[/TD]
[TD]Desired Output[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Completed[/TD]
[TD]SJR Tech 1[/TD]
[TD]100[/TD]
[TD]600[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Completed[/TD]
[TD]SJR Tech 2[/TD]
[TD]200[/TD]
[TD]600[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Under Construction[/TD]
[TD]SJR tech 3[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Completed[/TD]
[TD]SJR Tech 4[/TD]
[TD]300[/TD]
[TD]600[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Under Construction[/TD]
[TD]ABC Tech 1[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Under Construction[/TD]
[TD]ABC Tech 2[/TD]
[TD]300[/TD]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Completed[/TD]
[TD]ABC Tech 3[/TD]
[TD]400[/TD]
[TD]900[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Completed[/TD]
[TD]ABC Tech 4[/TD]
[TD]500[/TD]
[TD]900[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Completed[/TD]
[TD]DEG Tech 1[/TD]
[TD]600[/TD]
[TD]1300[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Completed[/TD]
[TD]DEG Tech 2[/TD]
[TD]700[/TD]
[TD]1300[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Under Construction[/TD]
[TD]DEG Tech 4[/TD]
[TD]300[/TD]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Proposed[/TD]
[TD]FGT Tech 1[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Proposed[/TD]
[TD]FGT Tech 2[/TD]
[TD]300[/TD]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Completed[/TD]
[TD]FGT Tech 3[/TD]
[TD]200[/TD]
[TD]400[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Completed[/TD]
[TD]FGT Tech 4[/TD]
[TD]200[/TD]
[TD]400[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Akshath]

Re: SUMIFS using conditions multiple Criteria. Please Helo

Hi,

Correction, I need to sum the areas when status is "Under Construction" as well

Example:

ABC TECH 1 & ABC TECH 2 = 500

 

[Aladin Akyurek]

Re: SUMIFS using conditions multiple Criteria. Please Helo

Something like:

=SUM(SUMIFS(Area,Status,{"proposed","under construction"},Name,"ABC*"))

 

[Akshath]

Re: SUMIFS using conditions multiple Criteria. Please Helo

The formula should take into account all the names in Column B and add the areas accordingly

 

[Akshath]

Re: SUMIFS using conditions multiple Criteria. Please Helo

I meant that the formula should compare B2, B3, B4 & B5 and sum the areas for status "Completed".

The formula should do the same for B6, B7 & B8 and sum the areas which are "Under Construction"

 

[Aladin Akyurek]

Re: SUMIFS using conditions multiple Criteria. Please Helo

I meant that the formula should compare B2, B3, B4 & B5 and sum the areas for status "Completed".

The formula should do the same for B6, B7 & B8 and sum the areas which are "Under Construction"

You are still vague, that is, not sufficiently precise.

=SUM(SUMIFS(Area,Status,{"proposed","under construction"},Name,"ABC*"))

This sums everything from Area where Status is either proposed or under construction and Name starts with ABC.

=SUMIFS(Area,Status,"proposed",Name,"ABC*")

would sum everything from Area where Status equals proposed and Name starts with ABC.

Hope this helps.

 

[Akshath]

Re: SUMIFS using conditions multiple Criteria. Please Helo

Firstly I appreciate the help.

The criteria is not only the Status column (Column A), but also the name column (Column B).

The formula should read the first few characters in cell B2 compare it to B3, B4 if it matches then sum up the areas, along with the additional criteria of "Completed" and "Under Construction"

The formula you have given, would only work for text containing ABC {=SUM(SUMIFS(Area,Status,{"proposed","under construction"},Name,"ABC*"))}

Please let me know if the above made sense.

 

[Aladin Akyurek]

Re: SUMIFS using conditions multiple Criteria. Please Helo

You must precise exactly what must hold for Area and what must hold for Name. What follows is again a guess...

In D2 enter and copy down:

=SUMIFS(Area,Status,IF(A2="completed","completed","<>completed"),Name,IF(A2="completed",LEFT(B2,3)&"*",B2))