# SUMIFS using conditions multiple Criteria

#### Akshath

##### New Member
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

 A B C D E F 1 Status Name Area Desired Output 2 Completed SJR Tech 1 100 600 3 Completed SJR Tech 2 200 600 4 Under Construction SJR tech 3 200 200 5 Completed SJR Tech 4 300 600 6 Under Construction ABC Tech 1 200 200 7 Under Construction ABC Tech 2 300 300 8 Completed ABC Tech 3 400 900 9 Completed ABC Tech 4 500 900 10 Completed DEG Tech 1 600 1300 11 Completed DEG Tech 2 700 1300 12 Under Construction DEG Tech 4 300 300 13 Proposed FGT Tech 1 200 200 14 Proposed FGT Tech 2 300 300 15 Completed FGT Tech 3 200 400 16 Completed FGT Tech 4 200 400

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

##### MrExcel MVP
Re: SUMIFS using conditions multiple Criteria. Please Helo

Something like:

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

#### Akshath

##### New Member
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

##### New Member
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"

##### MrExcel MVP
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

##### New Member
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*"))}

##### MrExcel MVP
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))

Replies
4
Views
76
Replies
3
Views
194
Replies
0
Views
258
Replies
4
Views
78
Replies
4
Views
157

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.

### Which adblocker are you using?

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

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