SUMIFS using conditions multiple Criteria

Akshath

New Member
Joined
Aug 13, 2018
Messages
11
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

ABCDEF
1StatusNameAreaDesired Output
2CompletedSJR Tech 1100600
3CompletedSJR Tech 2200600
4Under ConstructionSJR tech 3200200
5CompletedSJR Tech 4300600
6Under ConstructionABC Tech 1200200
7Under ConstructionABC Tech 2300300
8CompletedABC Tech 3400900
9CompletedABC Tech 4500900
10CompletedDEG Tech 16001300
11CompletedDEG Tech 27001300
12Under ConstructionDEG Tech 4300300
13ProposedFGT Tech 1200200
14ProposedFGT Tech 2300300
15CompletedFGT Tech 3200400
16CompletedFGT Tech 4200400

<tbody>
</tbody>
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Akshath

New Member
Joined
Aug 13, 2018
Messages
11
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
 
Upvote 0

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Re: SUMIFS using conditions multiple Criteria. Please Helo

Something like:

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

Akshath

New Member
Joined
Aug 13, 2018
Messages
11
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
 
Upvote 0

Akshath

New Member
Joined
Aug 13, 2018
Messages
11
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"
 
Upvote 0

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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.
 
Upvote 0

Akshath

New Member
Joined
Aug 13, 2018
Messages
11
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.
 
Upvote 0

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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))
 
Upvote 0

Forum statistics

Threads
1,187,106
Messages
5,961,626
Members
438,556
Latest member
darrylburge

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