1. Computing
Ted French

Excel SUMIFS Function

By January 24, 2012

Follow me on:

Excel's SUMIFS function is similar to the SUMIF function except that it is used to add up data only if specific criteria are met in two or more ranges of cells.

The function can be used to pick out data for specific years - allowing you, for example, to add up only the profits for the year 2006 and 2007 from a worksheet containing data from 2004 to 2008.

To find out the details, read the step by step tutorial on using the Excel SUMIFS Function.

Related Step by Step Tutorials
Comments
August 12, 2009 at 1:24 pm
(1) Jack Feehan says:

One difficulty I have in making this function work is substituding a range name that has a numerical value, say a range name “monthofsale” being 36 for the first criteria and summing to the “monthofsale”+12 being the second criteria. If I put in 36 and 48 the sumifs function works. If I put in the range name I get a value of 0. Any suggestions? I would like to use this function to sum the monthly net operating income for each month from the month of sale.

September 14, 2009 at 4:40 pm
(2) Michael Valenzuela says:

I ran across the same problem as you Jack.

I found a work around by “building” the string I wanted using the CONCATENATE function.

So you could do something like: SUMIFS(RANGE1, RANGE2, CONCATENATE(“=”, B$25) )

See this builds the criterion string, which would allow you to enter something complicated from your spreadsheet.

July 7, 2010 at 11:41 pm
(3) Scott Lent says:

I’m trying to use SUMIF where the “Range” is a list of dates, and the “Criteria” is a date in a cell on my spreadsheet.
It all works when I enter a date directly into the criteria, but when I try to make the criteria a cell value, it won’t work.
It works when I use the formula in this way:

SUMIF(‘Out Of Service’!A3:A48,”>11/13/2009″,’Out Of Service’!C3:C47)

If I try to use the value of 11/13/2009 in cell B4, it doesnt work:
SUMIF(‘Out Of Service’!A3:A48,>B4,’Out Of Service’!C3:C47)

Thanks

July 14, 2010 at 8:42 pm
(4) kevin says:

according to Michael ‘s method, you can use it like this:

SUMIF(’Out Of Service’!A3:A48,concatenate(“>”,B4),’Out Of Service’!C3:C47)

I have tried and it works.

November 23, 2010 at 9:04 am
(5) Pawne Khalil says:

Dear Sir,
i want to know about the formulla for sum same name in different cells like i have record of daily sale for 30 days out of which i want to make sum of A party sale from different cell . for example i sold him material for 25 days so i want to know about formulla which can help me to add all 25 entries and get final total for month

November 28, 2010 at 2:19 am
(6) prashant Singh says:

Plese tell me about lookup and pivote table how we make and design the sheet in pivote tabele and formula in pivote table

December 9, 2010 at 6:36 pm
(7) MAB says:

Scott
Forget Sumif and its quaint use of quotation marks. Use the mighty Sumproduct instead:

=SUMPRODUCT(–(‘Out of Service’!A3:A48>B4),’Out of Service’!C3:C48)

Needs to be 48 in both parts though, not 47 in one and 48 in the other.

Regards
MAB

January 27, 2011 at 5:29 pm
(8) AWM says:

I’m currently running a spread sheet that has I use to track our safety as a company. I’m attempting to sum the loss time accidents (LTA) by branch, year, and type of injury (LTA, JT/MD, MO, FA). So what I’d like the equation to do is this: Total number of loss days each branch has had. if three conditions are met: branch location G:G, year of the incident O:O, and type of injury AF:AF. I have the equation written as follows:

SUMIFS(‘Incident Compiled Data’!AL:AL,’Incident Compiled Data’!G:G,”AZ-PHX”,’Incident Compiled Data’!O:O,”2010″,’Incident Compiled Data’!AF:AF,”LTA”)

this equation should sum the loss time accidents if the LTA happened in the Arizona branch in 2010……however it’s returning the #value! error. Any help would be much appreciated.

April 26, 2011 at 12:48 pm
(9) Jordan says:

I am trying to sum a column of data in a data tab. This data to from comes from cells the main sheet. We put the start date and the last date in the cell so we want to do a > = and a =F1),(LoadData!$G$2:$G$3064 and < formula and it also didn’t work.

April 26, 2011 at 12:52 pm
(10) Jordan says:

try that again.

I am trying to sum a column of data in a data tab. This data to from comes from cells the main sheet. We put the start date and the last date in the cell so we want to do a > = and a =F1),(LoadData!$G$2:$G$3064 and < formula and it also didn’t work.

June 8, 2011 at 3:54 pm
(11) Maru says:

What`s SUMIF?

July 31, 2011 at 9:13 pm
(12) CARLO JAMES says:

thanks! this site is a great help!thank you very much

September 22, 2011 at 3:07 am
(13) Kipp says:

I have a table where I take the variances percentages between to columns for each row. Im trying to use the sumifs function to add the two column values that drive the variance calculation within a certain range of variation. Help!!!

September 26, 2011 at 6:41 pm
(14) Peter says:

I have a simple spreadsheet; column a is date in DD/MM/YYYY format and column C are dollar values.

How do I use SUMIF to total the numbers based on YEAR alone?

November 8, 2011 at 3:43 am
(15) Richard says:

Dear all,

I have a question for you.

When we tried to create SUMIF function where by accident we input into the range text. The result was incorrect in comparison with the one with correct range(no text only numbers). Any ideas why does it work out a wrong result?

Thanks for any ideas.

Richard

November 21, 2011 at 6:04 pm
(16) Jason Spielfogel says:

I’m trying to create a sumifs statement that sums the quantity of a part number sold during a certain date range. The main tab has the entire transaction per line with part number, qty and date shipped on each line. How do I create a =sumifs argument that shows me how much of a specified part number shipped during a specific month?

November 24, 2011 at 2:12 am
(17) twangi says:

AWM – I had same problem.

Trial and error told me that SUMIFS will give a #Value error if I refer to a part of the spreadsheet that has not yet been used, i.e. outside of the active range.

So it can’t refer to an entire column (A:A) because not all rows are yet used.

All SUMIFS examples I can find assume that you know the size of your sum/criteria range, but I (and presumably you also) need to handle a range that will change size, albeit sum and criteria will always be the same size.

The SUMIF was happy to work with column references… I have used it hundreds of times in this way.

Did you find a resolution?

November 24, 2011 at 2:25 am
(18) twangi says:

Jason Spielfogel -

I used this to solve what I understood to be your requirement.
i.e. Add quantities for a given month using rows which contain dates.

As an example:
- put dates in A2 to A5
- put quantities in range B2 to B5
- put first day of month in F1
- put last day of month in G1 (could be calculated from F1**)

=SUMIFS(B2:B5,A2:A5,”>=” & F1,A2:A5,”<=" & G1)

** I would use following to calculate end of month in cell G1:
=date(year(F1),month(F1)+1,1)-1

November 24, 2011 at 2:31 am
(19) twangi says:

Richard -

Excel evaluates text to zero.

Even if you enter a number, if Excel is interpreting it as text, it will evaluate to zero.

For example:

- enter the following into cell A1 including leading apostrophe:
’1

- enter the following into cell A2 including leading apostrophe:
’5

- enter =A1+A2 into cell A3

You will see the total is zero, so for formulas including SUMIF to work as expected your data must be being interpreted as the correct type.

November 24, 2011 at 2:41 am
(20) twangi says:

Peter –
“I have a simple spreadsheet; column a is date in DD/MM/YYYY format and column C are dollar values.
How do I use SUMIF to total the numbers based on YEAR alone?”

I would add a column (say B) to calculate year from column A, e.g. for row 2:

=year(A2)

(Caveat: Above formula will only work if Excel is interpreting your date as a Date Type rather than text.)

Then use sumif on that new column which just contains the year, e.g. for year 2011:

=SUMIF(B2:B10,2011,C2:C10)

November 24, 2011 at 2:52 am
(21) twangi says:

Kipp –
“I have a table where I take the variances percentages between to columns for each row. Im trying to use the sumifs function to add the two column values that drive the variance calculation within a certain range of variation. Help!!!”

So for example, we have Budget in column A, Actual in column B, Percentage Variance in column C:

- for variance column C I’m using in row 2:
=(B2-A2)/A2

To add column A and B where column C is (say) between 50% and 60% I used this:

=SUMIFS(A2:B4,C2:D4,”>=50%”,C2:D4,”<=60%")

** NB I have had to add a column D which is an exact copy of column C **

This is because SUMIFS has a rule that the ranges it refers to must all be the same size. So because your sum range has two columns, the criteria range must have two columns too.

If you do not want your column D to show up you can of course hide it.

November 24, 2011 at 3:04 am
(22) twangi says:

Jordan -

“I am trying to sum a column of data in a data tab. This data to from comes from cells the main sheet. We put the start date and the last date in the cell so we want to do a > = and a =F1),(LoadData!$G$2:$G$3064 and =F1 AND =F1 which would evaluate to just =F1.

I think you’re trying to do two criteria in one part of the formula by using ‘and’.
As far as I am aware you can only do one.
Just split it out into two criteria, SUMIFS lets you put up to 127 in!
You can refer to the same criteria range multiple times, for example assuming your amounts to sum are in column F, your value you are testing for Greater than or Equal to is in A1 and your value you are trying to match is in F1:

=SUMIFS(F2:F3064,G2:G3064,”>=” & A1,G2:G3064,F1)

However it sounds as if you could be falling foul of the problem noted above whereby SUMIFS will give a #Value error if you refer to an unused part of the spreadsheet.

It also sounds as if you are trying to sum by date range, if so hopefully the example above for Jason can help you.

November 29, 2011 at 1:53 am
(23) yan says:

Hi ,

I need some help as I have some forecast report to do can I use this formulas to get what figure I want .

Current formulas for the last Quater report as below : -

=+’31 Oct’!$C10+’30 Nov’!$C10+’31 Dec’!$C10

But if I only need year to month figure like now I need only to 30 Nov 2011 what should I do with the formulas . In order not to touch this formulas as not all my colleagues know excel . I just need a correct YTD figure when the month reach .

February 16, 2012 at 5:49 pm
(24) Scott says:

Trying to write a “sum formula” that would satisfy this scenario:

(Add contents of column A) IF value in column B (of the corresponding row) meets certain criteria.

For example:

Column A

$5
$10
$15
$20

Column B (corresponding rows)

January
January
February
January

So, sum of column A when the corresponding row in column B = January.

Hopefully that makes sense. A pivot table would probably make this easier, but the project I’m on isn’t based on a pivot table.

March 5, 2012 at 8:06 am
(25) Dawn says:

Help please. I am trying to update a spreadsheet that we used last month with the following formula in:

=SUMIF(‘MARGIN Data’!$C:$C,A9,’MARGIN Data’!$L:$L)

We are now in Feb, and therefore ahve Jan and Feb figures to add together and pull into the spreadsheet. So I tried:

=SUMIF(‘MARGIN Data’!$C:$C,A9,’MARGIN Data’!$L:$M), but of course it does not add the column values.

Can someone help me please? I need to get this finished this afternoon if it all possible.

Dawn

May 3, 2012 at 12:01 pm
(26) Matt Beeman says:

Michael Valenzuela (Comment #2), you are my hero, that just saved me hours of time.

May 3, 2012 at 4:54 pm
(27) kar says:

twangi, your response to Jason Spielfogel (#18) helped me tremendously. Thanks very much!

July 4, 2012 at 3:59 pm
(28) TMM says:

I am having the same issue with SUMIFS where I can put a my criteria directly (dates) into the formula and it works but when I try to point to a cell with dates it does not. I have use a couple of the above and nothing is working.

This works…
=SUMIFS(’2012DataInput’!C2:C367,’2012DataInput’!$B$2:$B$367,”>=6/30/2012″,’2012DataInput’!$B$2:$B$367,”=” & $B$3, ’2012DataInput’!$B$2:$B$367, ”>=” & $D$3)

=SUMIFS(’2012DataInput’!C2:C367, ’2012DataInput’!$B$2:$B$367, concatenate(”>”,B3), ’2012DataInput’!$B$2:$B$367, concatenate(”>”,D
4))

Any help is appreciated..

July 26, 2012 at 5:06 am
(29) Pankaj says:

I have data that contains the date range from 2010 to 2011 now i would like to sum the value which is in column B and all dates are i n Column A

November 7, 2012 at 4:04 pm
(30) Bredth says:

How (what function) can I retrieve a value from a different cell in the same row from a matching cell.

I hope I can explain this right.

I want to be able to first find a match (number) from an array of cells (a whole long column of cells). Then if there is a match, take the value from a different cell in the same row of the found matched cell and put that value into a new cell.

Can this be done?

November 16, 2012 at 4:14 am
(31) matty says:

Hi im struggling a little i have a data table and am tring to find the sum of the data when linked to a county, so when ever the work “cleveland” appears in one column it returns with the sum of numbers in another column, i have managed to do this using a basic SUMIF formula, however my question is, is there a way i can limit the results to the first 6 “cleveland” results that show up and forget the rest that proceed after. basically i only need to sum up the first 6 set of results.

Many thanks

Matty

December 30, 2012 at 11:03 am
(32) Shan says:

Hi, I need help on a sumifs function.
I get the formula correct which are:
cell C being =COUNTIF(‘December Sample’!D:D,”Mahmoud Khattab”)
data coming from a separate tab and
Cell D being =SUMIFS(‘December Sample’!H:H,’December Sample’!D:D,”Mahmoud Khattab”)/(C4)
My second formula is returning a #DIV/0! which i understand as a result of having 0 divisor. can anyone please help in figuring out what formula i have to use or what kind of formatting should i do to hide or replace error with zero or -?

January 16, 2013 at 11:13 am
(33) Robert says:

I am using the SUMIF function to create a table that will sum up how many funds are starting in my department, but I need this to be based on MONTH.
The launch dates are formatted as “January 31, 2013″ etc., I want to be able to say if the launch date column contains “January” then to sum up the # of funds column.
Any help here?

January 23, 2013 at 8:54 am
(34) Khurram says:

Can I give a range with cell value instead of cell number?

I have two columns A & B), A has time like 09:00, 11:00, 12:00, 13:00 ….
and B has values in front of each time. I need to get sum of B in column C if B has any value between 11:00 to 12:00.

Thanks,

May 29, 2013 at 8:26 am
(35) Rajesh says:

I have dates in one row and amount in second row. I want to consolidate amount based on the month in a summary sheet. Out put needed is below:
JAN-13 FEB-13 MAR-13
$24 $50 $100

Data is as below:
1/1 1/10 2/15 2/20 3/6 3/20
12 12 25 25 50 50

I am trying to use SUMIF but I am unable to put an expression in the second/3rd parameter to match the month name. Please suggest.

August 29, 2013 at 9:47 am
(36) Charle says:

Can someone please help me. I am getting Value errors when I try to use this SUMIFS formula. Just trying to extract all amounts over $100K in the H column. Everything else in the formula worked fine before I added this criteria. Thanks a lot!

=SUMIFS(Data!$H$3:$H$34710,Data!$H$3:$H$37410,”> 100000″,Data!$AB$3:$AB$34710,Summary!E$3,Data!$AA$3:$AA$34710,Summary!$B$69,Data!$AC$3:$AC$34710,Summary!$C70,Data!$U$3:$U$34710,”*intergov*”)

November 16, 2013 at 7:25 am
(37) Sirajudheen says:

I have created a SUMIFS formula =SUMIFS($G$17:$G$3225,$E$17:$E$3225,”=26-Aug-12″,$B$17:$B$3225,”Ready Mix C 10/20″)

I want to add this formula from date 25-Aug-12 to 31-Dec-2014 how to add this

DATE R/Mix C 10/20
Qty (m3) Temp. Control Pump (m3)

25-Aug-12 12
26-Aug-12 11
27-Aug-12
28-Aug-12
29-Aug-12
30-Aug-12
31-Aug-12
01-Sep-12
02-Sep-12
03-Sep-12
04-Sep-12
05-Sep-12
06-Sep-12

Leave a Comment

Line and paragraph breaks are automatic. Some HTML allowed: <a href="" title="">, <b>, <i>, <strike>

©2014 About.com. All rights reserved.