Summing Cells Given a Range and IF Statement

[hhound4]

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.

 

[Eric W]

Try:

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

 

[hhound4]

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]

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]

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]

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

 

[hhound4]

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

 

[Eric W]

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]

That works great, perfect for what I need, thanks!

 

[Eric W]

You're welcome.