# Summing Cells Given a Range and IF Statement

#### hhound4

##### New Member
I don't know how to paste a screenshot but I will try to describe best the situation I need a formula for. This is a form that tracks company expenses.

Column "E" will have a range of numbered codes that the person submitting the form selects depending on type of expense. Let's say it code be code "96100" or "71000" or "63100".

In another column, column "B", keeps track of the cost of the expense. Cost could be anything and varies greatly.

I would like to be able to paste in a formula that adds up all the expenses that correlate to each expense code. So a total for "96100" and "71000" separately.

I have tried to do this using SUMIF function but can't seem to get it. Any help would be great.

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### Eric W

##### MrExcel MVP
Try:

=SUMIF(E:E,96100,B:B)

#### hhound4

##### New Member
Try:

=SUMIF(E:E,96100,B:B)

Thanks Eric that works well and I'm not sure how I missed it!

Now is there a way to add an "AND" function in here? We have 3 different divisions in our company.
So I would like to determine, the expense type, the expense amount (which is handled so far) and the division.

So in another column, column "N", it will be division "CORP" or "INT" or "DOM" depending on what the user selects.

Is there a way to add that into the function? thanks again for your help!

#### Eric W

##### MrExcel MVP
If I understand correctly, you're looking for SUMIFS (note the final S). This was added in Excel 2007.

=SUMIFS(B:B,E:E,96100,N:N,"CORP")

Also note that the Sum range has been moved to the first parameter position. That function will look down column E and N, and if on the same row the values are 96100 and "CORP" it will include the B value from that row into the total. You can of course put the type and division in a cell, maybe a drop-down, say in X1 and X2 and change it to:

=SUMIFS(B:B,E:E,X1,N:N,X2)

and you'll get a dynamic function.

Happy to help!

#### Anand Sharma

##### Board Regular
Hi

 a b c Formula Ncorp 96100 10 10 Bcorp 71000 10 10 Ncorp 63100 10 20

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

Try =SUMIFS(\$C\$2:\$C\$9,\$A\$2:\$A\$9,A2,\$B\$2:\$B\$9,B2)

Sumifs is useful when you have more than one condition to sum up a value

#### hhound4

##### New Member
Thanks to both of you. This helps me a ton in my work, greatly appreciated

#### hhound4

##### New Member
If I understand correctly, you're looking for SUMIFS (note the final S). This was added in Excel 2007.

=SUMIFS(B:B,E:E,96100,N:N,"CORP")

Also note that the Sum range has been moved to the first parameter position. That function will look down column E and N, and if on the same row the values are 96100 and "CORP" it will include the B value from that row into the total. You can of course put the type and division in a cell, maybe a drop-down, say in X1 and X2 and change it to:

=SUMIFS(B:B,E:E,X1,N:N,X2)

and you'll get a dynamic function.

Hi Eric,

Okay so I have the formula setup to use 4 different criteria and it has been working.

In this example below, the formula sums up column "E" if column "M" matches code "63100", column "N" matches "INT", column "O" matches "INT" and column "P" matches "-". This formula works well.

=SUMIFS(VisaReport!E7:E62,VisaReport!M7:M62,"63100",VisaReport!N7:N62,"INT",VisaReport!O7:O62,"INT",VisaReport!P7:P62,"-")

But 1 issue I need to solve. I need to also sum all amounts that are not equal to "-" in column "P". So the first 3 criteria remain the same, but I need to split column "P" into 2 different summaries. When "P" = "-" the above formula handles that total and when "P" equals anything other than "-" then the below formula will handle that (when corrected).

=SUMIFS(VisaReport!E7:E62,VisaReport!M7:M62,"63100",VisaReport!N7:N62,"INT",VisaReport!O7:O62,"INT",VisaReport!P7:P62,<>-)

Can this be achieved? Does using the "-" symbol create a problem?

Thanks again!!!!

Last edited:

#### Eric W

##### MrExcel MVP
You're very close! These SUMIFS formulas trip up even long-time users. The way the parameters are defined is a little different from other functions. Try:

=SUMIFS(VisaReport!E7:E62,VisaReport!M7:M62,"63100",VisaReport!N7:N62,"INT",VisaReport!O7:O62,"INT",VisaReport!P7:P62,"<>-")

This version is a bit longer, but it shows a concept that is useful sometimes:

=SUMIFS(VisaReport!E7:E62,VisaReport!M7:M62,"63100",VisaReport!N7:N62,"INT",VisaReport!O7:O62,"INT") -
SUMIFS(VisaReport!E7:E62,VisaReport!M7:M62,"63100",VisaReport!N7:N62,"INT",VisaReport!O7:O62,"INT",VisaReport!P7:P62,"-")

#### hhound4

##### New Member
That works great, perfect for what I need, thanks!

You're welcome.

Replies
1
Views
130
Replies
16
Views
186
Replies
1
Views
159
Replies
1
Views
225
Replies
2
Views
493

1,191,171
Messages
5,985,067
Members
439,938
Latest member
MAlhash

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