SubscribeIntroduction to Excel SUMIFS Formula
If SUMIF formula is like a regular knife that you can use to slice up your data and get what you want, then SUMIFS is like a Swiss army knife.
What is SUMIF?
We all know that SUMIF formula helps to findout the sum of all values meeting a condition. So, if you have data like below
and you want to find out how many Spit Bombs you sold (spit bombs, really?) then you can use SUMIF like this:
=SUMIF(B3:B16,"Spit Bomb",D3:D16)
But as you guess, SUMIF has a serious limitation. It can only process one condition at a time. So if you want to find out how many Spit Bombs are sold in North, you have to,
- Drive down to North and meet the victims to take count of spit bombs sold
- Use array formulas to SUM based on multiple conditions
- Use SUMPRODUCT formula
While the first option takes a lot of time, the other two involve writing complex formulas.
So What is SUMIFS Formula?
SUMIFS is the plural version of SUMIF. Using SUMIFS you can find the sum of values in your data that meet multiple conditions.
So, to get the sum of all the spit bombs sold in North, we just write,
=SUMIFS(D3:D16,B3:B16,”Spit Bomb”,C3:C16,”North”)
Similarly to find the podgun sales in East, just write,
How Does SUMIFS Formula work?
SUMIFS formula takes a range for summing the values and at least one criteria range and criteria. You can specify as many as 127 conditions for summing your data.
Imagine asking “how many spit bombs Hansolo sold in North region of Planet Naboo between long long ago and long ago that resulted in more than 25% profits?” and getting an instant answer.
The beauty of SUMIFS formula is that it works with wildcards too, just like its siblings – SUMIF and COUNTIF. So you can write formulas like,
=SUMIFS(D3:D16,B3:B16,”Spit Bomb”,C3:C16,”*th”) to get sum of spit bombs sold in North and South.
So what is the catch?
While SUMIFS seems like all butterflies and berries, there is a catch. It works only in Excel 2007 and above. But with Excel 2010 in the horizon, I have high hopes that finally everyone will be able to enjoy the SUMIFS formula.
Bonus:
Just like SUMIFS, there is COUNTIFS and AVERAGEIFS too in Excel 2007+. I am sure you already know what they mean.
| ||||
|
| ||||
|
Leave a Reply
 |
How to Select Right Chart for your Data | Budget vs. Actual Profit Loss Report using Pivot Tables |  |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
99 Responses to “Introduction to Excel SUMIFS Formula”
Very useful, thank you!
Must… force.. IT-department… to UPGRADE THIS SORRY MESS TO Win7 & Office 2010!!!
*drooooooool…..*
I’ve been working with sumif formulas today and have been wondering how I can use 2 criterias or more. I have been reading about Dsum and Daverage. But, of course, this is much easier.
So thanks!
Of all the tips you’ve posted thus far, I can’t think of one that will be more beneficial to me in my day-to-day. Wanting to include multiple criteria in my SUMIF formulas has long been a serious point of frustration.
Question: How will this formula be handled by the conversion tool if someone opens my 2007 file with and earlier version of Excel?
Thanks for sharing, Chandoo.
I use these formulas a lot. I hope 2010 adds a bunch of additional ones. Don’t forget that the criteria can be referenced in from cells outside the formula. This is really helpful for building a summary table. As an example of the versatility of the formula, using Chandoo’s table, this formula =SUMIFS($C$2:$C$15,$A$2:$A$15,A2,$C$2:$C$15,F7) give a sum of 509 when the criteria value in A2 is “Pod Gun” and the criteria value in F7 is “>130″. Also, wild cards can be used in the criteria formula… setting the “A2″ cell reference to “*un” yields the same result.
The astrick argument is gold. Saved me. Thanks Bill
@Ducheznee: Thank you. When you save a file with SUMIFS and open it in Excel 2003, the cells containing SUMIFS should show #NAME error (as the sumifs formula is not available in those versions). You can use one of the other methods (like SUMPRODUCT http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/ or SUM array formula http://chandoo.org/wp/2009/07/22/sumif-with-multiple-conditions/ ) to handle such cases.
@Bill.. thanks
@Jan Hogh: Totally agree with you. After using Office 2010 and Win 7, I dont feel like looking at windows xp comps.
@Cuboo and Finnur: Thanks
You know it’s funny. When Excel 07 came out, I found I had to stop using SUMIFS because so many people were still using 03, but then you rescued me with SUMPRODUCT Chandoo! =)
Hi and 100 more
as usual very impressive and cool especially with win7+office2010
Many Thanks
What could be reason for not moving to Excel 2007+ as even large multinationals are also stuck with Excel 2003 when nobody has any doubt about their superiority over previous versions. So what might be the reasons behind this reluctance to adopt this much better product over an obsolete and less relevant in todays environment even by large multinationals?
The larger the corporation the more applications are embedded and the easier it is to break things by up-editioning. Induhviduals can fix broken logic however when you have 35,000 computers introduce errors jumping to the next version (no matter how great it is) ALWAYS get me reaching for the hoiday booking form!
[...] The SUMIF swiss army knife April 26, 2010 at 5:42 PM | In General | Leave a Comment Tags: formulas, sumif, sumifs Chandoo wrote a very interesting explanation on how to use the SUMIFS function. [...]
more and more i am using SUMIFS rather than VLOOKUP. it’s invaluable when you want to supply multiple criteria in a table that has more than one or two dimensions. of course you have to make sure that each table entry is unique.
I am a teacher and need some help….how do I create a multiple sumif formula:
I want to count/sum scores if they meet two criteria: Quiz and >=0
If a student is Absent “A” I dont want it to affect their average. Any insight on an easy fix would be great. Thanks!
[...] we know how to find sum of values that meet a criteria – we use either SUMIF, SUMIFS or SUMPRODUCT [...]
@ bill – you just saved me a TON of time! I hope I will pay it forward on your behalf. Thank you.
Hi, Iam preparing a salary bill of an employee for a period form July-2008 to June-2010. In this mean time he got promotion on 18-01-2009. From that day his salary hiked from Rs.10030 to Rs.10565. He got one increment on 18-01-2010. That day onwards his salary again enhanced from Rs. 10565 to Rs.11115. I prepared this bill in excel-2007 worksheet. I wish to split the salary in the months when he got hike in his salay, with excel formulas. But I am unable to do it. Can ypu help me please?
Being first time in chandoo.org i loved it sooooo much … i become a great fan of chandoo cos its so simple and huge knowlege giving sites i have ever seen ..thanks a lot man ..god bless you
Hi Chandoo,
I’ve been following Chandoo.org for some time now. Great work!
Slight query regd. this post-
I’ve used SUMIF\SUMIFS in the past, but I do have one grouse. The need for putting criteria in double quotes means I can’t pull criteria from a cell reference which would make it much more powerful. i.e., instead of criteria “>150″ how do i use criteria >$A$1 ? Some help please?
Thanks, Anish
@Gouse.. you can use SUMIF or SUMIFS to sum all the salary amounts prior to promotion etc.
@Jawad: Thank you
@Anish: you can simply type >150 in A1 and then use that in formula like =sumifs(range, a1). If you cannot do that, you can use B1 to concatenate A1 with the operator you want.
Hey Chandoo,
Thanks for that tip, Just tried it and it works! Nice!!
Thanks again!.
useful thank a lot but if u could more examples as a challenging i would be much better
Hi Chandoo,
Excellent review of the new SUMIFS function!
Regarding the catch you mentioned above…
If you want to send Excel files to users of previous versions you must convert SUMIFS. To make Excel 2003 users see results instead of an error the user must use SUMPRODUCT or Array formulas.
This formula: =COUNTIFS(‘Sales’!$A$4:$A$400,”JAN”,’Sales’!$V$4:$V$400,1)
Turns into this one: =SUMPRODUCT((‘Sales’!$A$4:$A$400=”JAN”)*(‘Sales’!$V$4:$V$400=1))
This link may help:
http://www.excel-spreadsheet-authors.com/sumif-multiple-7-ways.html/
I am trying to reuse the same criteria range but different criteria using SUMIFS; however, after establishing the first criteria range and entering the same for the second criteria range the result is zero. Is there a structure to reuse the same criteria range or include more than one criteria with the first criteria range? The data source structure is static but the values always change and I need to compare two data sources by sum.
@Tuttler… Can you give me an example of what you are trying to do. I am unable to visualize your problem.
The basic structure I tried using SUMIFS is: =SUMIFS(C:C,A:A,”Value A”,A:A,”Value B”,etc…). In order to get the result of using the same set of criteria_range, I used: =SUMIF(SUMIFS(C:C,A:A,”Value A”),SUMIFS(C:C,A:A,”Value B”),etc…)). Other criteria was used within each SUMIFS to further define the specific data set needed. Depending on the number of items required from the criteria_range the formula gets cumbersome.
[...] Using SUMIFS Formula [help] [...]
Hi Chandoo – great site! To follow up Tuttle’s question, let say I wanted to determine podguns sales in the North AND the South.
One way to try is maybe sumifs(podguns in the north) + sumifs(podguns in the south) although that might get cumbersome, I wonder if it might make more sense to try a dsum instead? I’m still in awe of / a little afraid of array functions {} but maybe you can use one as well?
Ravi
Hi,
Maybe you can help with a problem.
I want to use sumifs to calculate sums based on 1 criteria being today’s date.
I am using the =Now() formula to automatically update the date. However, it is not summing. If i replace the =now() with a hardwritten date, it works.
Any suggestions?
Thanks,
@Harris,
try using =Today() instead of =Now()
Today = Date
Now = Date and Time
[...] SUMIFS & COUNTIFS Formulas [...]
Dear Chandoo
Sumifs work great,
but there is one short coming, one column one criteria
I cannot get sum of two different months
SUMIFS(Value,A19:A151,”April”,A19:A151,”May”)
Sumifs gives a “0″ when i put a second criteria
I want to sum the sales of multiple/few months, the months are in Column A
ie one column multi criteria
Please Help
@Johnson Mathias
This is a short-coming in your logic not in Sumifs functionality
.
A cell in A19:A151 cannot be both April and May which is what you have asked Sumifs to do
.
Your query should be like
=SUMIFS(Value Range,A19:A151,”>”&DATE(2011,4,1),A19:A151,”<"&DATE(2011,5,30))
.
Give that a go
why is that Sumif, sumifs, or sumproduct formulas not automatically update unless the source file is opened.
They work fine same workbook but when external data source is involved it only work when source file is open.
Please help help help!!!
@Uzair
There is a good article at Daily Dose of Excel on this which is worth a read
http://www.dailydoseofexcel.com/archives/2004/12/01/indirect-and-closed-workbooks/
.
The comments also add other options
.
ps: There is no need to post here and in the Forums, they all get read
[...] I then placed the same item indexes in data!A7:A46. This is all because I am lazy and I find the sumifs() formula a blessing: all I have to do now is to add up the results that correspond to (1) the chosen Product [...]
Hui,
I have a similar issue than 27), but I’m trying to have the criteria “Date” as a variable referred to in two separate cells. So I can define date ranges as I need.
Sumifs gives a “0? when i put a the criteria
Please help!
@Matias H
You can use something like
=SUMIFS(B19:B151, A19:A151, ”>”&D1, A19:A151, ”<"&D2)
Where D1 and D2 are the dates you want
Thanks so much!!
[...] Introduction to SUMIFS formula [...]
Using Excel 2007 I have changed and now almost exclusively use SUMIFS, its easier, and doing multiple criteria is quicker. I can track Date, Country, Dustributor and on and on easier with SUMIFS, that using SUMPRODUCT.
Hi Chandroo (or whoever can help me!
)
I need help – am very new to more advanced calculations in excel.
My problem: I want to use ‘sumifs’, however the cells I want to add to the “sumrange” are not together (i.e.: c10+c6+c2+d2+e2) and want to include the conditions of sum only when c10 is not 0. I continue to get an error when I put this formula:
SUMIFS(C10+C6+C2+C2+E2,C10,”0″) and off course an error when I put the full formula I want which is something like this: SUMIFS(C10+SUMIFS(C6+C2+D2+E2,C6,”0″),C10,”0″)
Any help will be very much appreciated!
EA
@EA
=IF(C10<>0,SUM(C2,E2,C6,C10),0)
Thanks a lot Hui!
I’ve reached the limits of my logic & after an hour of fiddling & researching have nothing to show for it.
How do you select multiple criteria in a criteria range? Column A has sales person name, in some cases I need to sum together more than one sales person.
When you enter the same range for both Criteria_range1 & 2 the result is 0. I’ve played with = combinations which works but is incorrect as Excel thinks I’m after a range.
In effect what I want to do is:
=SUMIFS(WEBI_LY,WEBI_BDM,”fosters group”,WEBI_BDM,”sean crowe-maxwell”,WEBI_BRAND,C3,WEBI_BUNDLE,D3)
Many thanks in advance guys!
@AP
Your trying to do this:
=SUMIFS(B2:B11,A2:A11,”a”,A2:A11,”b”)
What that says is Sum Column B when Column A = “a” and Column A = “b”
A single record can only be one or the other it can’t be both
.
You need to use something like:
=SUMIFS(B2:B11,A2:A11,”a”)+SUMIFS(B2:B11,A2:A11,”b”)
.
In your case
=SUMIFS(WEBI_LY, WEBI_BDM, ”fosters group”, WEBI_BRAND, C3, WEBI_BUNDLE, D3) + SUMIFS(WEBI_LY, WEBI_BDM, ”sean crowe-maxwell”, WEBI_BRAND, C3, WEBI_BUNDLE, D3)
this exercise would be easily solved if Excell had an “or” function as in ”fosters group” or ”sean crowe-maxwell”. But assuming this is not the case, how do you solve this if instead of the above 2 conditions, there were 5, or 6? the function would indeed be long, tedious and prone to errors. In other words, what I’m really looking for (as well as other users I suppose) is a filter to be expressed as a function. Last but not least, very valuable posts.
@Fred
You can do this in one formula using Sumproduct
=SUMPRODUCT(((A2:A11,”a”)+(A2:A11,”b”)),B2:B11)
The + is effectively saying or
so Where A2:A11 = a or b sum B2:B11
@Hui | Thanks mate! Had that as my work around whilst awaiting a response here, was just hoping for something a little cleaner. All cool!
excellent-the example really helped
Hi All,
trying to resolve this, i need my criteria 1 to be equal either 4 or 5. The logic is correct, just not returning the results
SUMIFS(E6:E108,C6:C108,AND(4,5),I6:I108,A122)
Thanks in advance
@Jackie
I hate to disappoint but the logic is a little bit out
What you have asked for is that a cell in Range C6:C108 has a value of 4 and 5, which a cell obviously can’t have.
And/Or aren’t really suitable for use like this, although at first glance it does seem like they should be.
.
I’d recomend the following:
=SUMIFS(E6:E108,C6:C108,4,,I6:I108,A122) + SUMIFS(E6:E108, C6:C108, 5, I6:I108, A122)
Hi, Is it possible to have the SUM_RANGE as a cell reference with the cell reference being a VLOOKUP formula result)?
For example: data downloads from another program each month into sheet 1, with month 1 in (say) col D, month 2 in E etc. so it grows column wise each month. In sheet(2) is a table of 12 months with corresponding column references ( cell a1 has 001.2011 and cell b1 has “D:D” , a2 has 002.2011 and b2 has “E:E”. In sheet 3 Z1 is a list ( being a1 to a12 dates. In Z2 is a Vlookup formula which looks up the relevant column reference (ie list choice 002.2011 gives “E:E” in Z2. In cell Z8 is the sumif formula : =SUMIF($A:$A,”wotnots”,Z2). This should look at col A for wotnots and choose col E (month2).
The cell accepts the formula but doesnt give a numerical result. Iam wanting to be able to change sumif parameter using list choice.
Hi,
This related to Excel 2007. I have a sheet with multiple columns. Column B has dates (dd-mm-yyyyy) ranging from 1st to 31st of the month depending on the month. Column H has the amounts I want to sum when they are greater than zero. Each date can have multiple rows so the totals row is dynamic but the column rows are generally static.
I want to sumif on the Column B values (day less than 25th) and Column H, as indicated, for values greater than zero.
So far, my formula which sits a few rows below the total in Column H is not working:
=SUMIF(B6:B164;VALUE(LEFT(TEXT(CELL(“contents”);”TT MMM JJJJ”);2))”<25";H6:H164)
I've stripped it back but the quotes seem to be giving me headaches? Any ideas?
Kind regards,
Philip
does it have to be a sumif? how about a sum with ifs and curly brackets?
=SUM(IF(TEXT($b$6:$b$64,”dd”)*10,$h$6:$h$164))))
then hit ctrl shift enter for the array curly brackets?
Ravi
Hi Philip
Regarding post 44) Philip November 21, 2011
Your table seems to be A6:H164
Try
=SUMIFS($H$6:$H$164,$B$6:$B$164,”0″)
Works like a charm
part of formula above is getting edited as HTML
SUMIFS($H$6:$H$164,$B$6:$B$164,”=25-11-2011″,$H$6:$H$164,”=0″)
Change the = signs with greater than & less than Signs
Dear Ravi and Johnson,
Many thanks for your responses. Uhnfortunately, I’ve been too busy to try them out but as soon as I do, I’ll let you know. @Ravi: I don’t see where your formula is checking that the B column value is less than 25? Whether it is a SUMIF is for me secondary. It just has to before the logic I explained. Note that as I explained the 164 value is dynamic (in my fomula I actually use the “row()- 1″ instead of 164. I just put the 164 to reduce the number of brackets.
@Johnston: only the 25 (day) is static, the month and the year change.
how i can do sumif function in excel with tow condition in the same criteria, for example: sumifs of any rang with condition 4=< X <=6 .
thank you.
Hey Philip
I did not realize the day is static 25
One solution would be to add a Say “J” column called “Day” =day(B1)
Thus you get a usable day criteria
SUMIFS($H$6:$H$164,$J$6:$J$164,”=25?,$H$6:$H$164,”=0?)
Change the = signs with greater than & less than Signs
I hope this helps
To those wondering how to use an operand with a cell reference (and to the extent it wasn’t already posted) you simply concatenate the operand in quotes with the cell reference using an ampersand. So if your condition is less than or equal to the contents of C5, you type:
“<="&C5
Hui, you seem to recommend summing the result of two SUMIFS with slightly different criteria to satisfy the need for what is essentially an OR() function. Can you use an AND() or an OR() as a condition, or is yoru solution the only way to accomplish that?
My third and final post today: I answered my own question using Hui’s comments above. To satisfy an OR condition (such as numbers between two values) you just repeat the range to be evaluated twice, once with a “>=” condition and once with a “<=" condition.
Also, gotta give the obligatory shoutout…Chandoo's great!
Is it possible to use a cell reference in the range and sumrange in a sumif? I would like to type the sheet I want to reference in a specific cell and have the sumif formula reference that cell for the sheet information.
Misty, I use sumifs whose source data is on a different sheet all the time, I’ve also done it across workbooks. I’m not sure what you’re using the spare cell for as a reference though. Can you give an example of what you’re doing? I don’t want to give an overly wordy and unhelpful reply.
Hi Chandoo!
I have a problem….I have an Excel 2007 Sheet with Name of Dealers in Rows and Month wis Sales in Columns (Apr.10, Apr.11, May.10,May.11….Mar.11,Mar.12). I have a Dashboard Sheet where the user enters the starting month & ending month for e.g. Apr.11 TO Sep.11.
I want to calculate the No of Dealers having registered some sales in the period i.e. Apr.11+May.11+…..Sep.11 should not be Zero. This needs to be calculated dynamically depending on the value entered in the Dashboard Month Field.
@Vineet
No need to double post.
This has already been answered in the Forums
http://chandoo.org/forums/topic/counting-no-of-rows-with-non-zero-totals-over-non-contiguous-columns-dynamically
Hi Chandoo
Great explanation of SUMIFS. But for summing up the values where data are spanning across multiple columns (e.g., data in 23 columns D:Z), is there a simple way? One could always write the SUMIFS statements for each column, using multiple criteria (e.g., criteria in 3 columns A:C), and then combine the results through simple addition, but this is not very elegant and in this case would require addition of 23 terms rather than one.
For example, this sums one column (and could be repeated, tediously):
=SUMIFS(D2:D10,A2:A10,A1,B2:B10,B1,C2:C10,C1)
but this returns and error, rather than summing 23 columns:
=SUMIFS(D2:Z10,A2:A10,A1,B2:B10,B1,C2:C10,C1)
Thanks for your input and help.
Sorry, but I had mistyped my email address a minute ago (linked to my question on using SUMIFS across multiple columns). This time it is correct.
Wow, great time saver. Thank you. I used it for the wildcard with the sumif.
Normal
0
false
false
false
EN-US
X-NONE
X-NONE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:”";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:”Calibri”,”sans-serif”;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:”Times New Roman”;
mso-bidi-theme-font:minor-bidi;}
Is there any way to get SUMIFS to do either/or criteria?
I was only able to get it to work with an array constant:
=SUM(SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,{“A”,”B”,”C”}))
However, arrays constants can’t use references like F1,F2,F3.
Is the ONLY alternative to use SUMPRODUCT (much slower) or to use multiple SUMIFS (very long formula and high maintence).
Lawrence
Yikes…what happened to my post?
How do I edit out all that code???
~L
Thanks, if everything was explained like this, everything would be easier to understand and do.
works just great, but I need to add an ampersand & to it to concatenate:
=SUMIFS($D$2:$D$15,$B$2:$B$15,”bli”,$C$2:$C$15,”>”&50)
Without the ampersand does not work at all with = or > or >=, just with a number. No matter if I put that in single or double or without marks. But with & works nicely. Sure I will use the sumifs quite some times.
So that would make Sumproduct the
of Swiss Army Knives
give some more range.
I do stuffs like this in MS Excel 2003 by using the sumif + concatenate function. Where concatenate resides in new column. =sumif($A$14:$A$27,”Pod GunEast”,$C$14:$C$27) where Column A is concatenation (&) of Column B and C.
[...] Introduction to SUMIFS formula, examples & explanation [...]
Its very Helpful!! thank you
Hey Chandoo, you are really awesome!! Now I will use sumifs instead of sumproduct!
Thanks a million!!
Hello There. I found your blog the usage of msn.
This is a very neatly written article. I’ll be sure to bookmark it and return to learn more of your helpful information. Thanks for the post. I will certainly return.
Thanks Chandoo. It was very useful. No need to make a Pivot.
Is there any reason why a sumif statement will not work on a text cell with a < or > symbol in it? I’m using these symbols to put data into buckets: >12 weeks, >26 weeks.
Cell A1: >12 weeks
=SUMIFS(Value to Sum,criteria range,A1)
=0
Am I stuck with changing all the text to ranges: 12-25 weeks, 26-52 weeks?
@JustiinCBU
Can you post a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
dear sir,
plz help formula us to 2003
sumifs in summary sheet
me formula is
sumifs(‘sheet 3 (cell s:s(value),’cell e:e(div),’summary sheet1 (cell c5 (ctv),’(sheet3(cell aa(status),summary sheet1 (cell c8 (applied),’(sheet3(cell k(month),summary sheet1 (old)
@Anil
Can you post a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
[...] Learn more about SUMIFS formula. [...]
Thanks Chandoo
Your excel tips r really awsome & making us awsome.
Can anyone help me out in this following condition:-
Suppose I want to make different sales sheets each of Pod Gun, Blow Torch & Spit Bomb. Is it possible that as I type the entry in general table (containing all products), the entry also goes to its respective excel sheet ?
Would b thankful
What if you want one of the criterias to pull from whatever is in a particular cell to the left?
Right now my last criteria is unique customer names that I have to type, but I would rather the last criteria be a cell reference so that I can just drag the formula down.
Hey Ella, see my posts above for using cell references and creating or conditions. I pasted one of them below:
“To those wondering how to use an operand with a cell reference (and to the extent it wasn’t already posted) you simply concatenate the operand in quotes with the cell reference using an ampersand. So if your condition is less than or equal to the contents of C5, you type:
“<=”&C5 “
we can also use sumproduct same a sumifs right????
Dear Chandoo,
I don’t have my own business neither appointed at a prominent position, despite of these shortcomings i’ve designated as “CEO” in the organization i’m employed at, and i acknowledge that its just b’caz of people like you. I’m fortunate to have you.
CEO = Chief Excel Officer
Wish you all the best!
Thanks.
Dear Chandoo,
I don’t have my own business neither appointed at a prominent position, despite of these shortcomings i’ve designated as “CEO” in the organization i’m employed at, and i acknowledge that its just b’caz of people like you. I’m fortunate to have you.
CEO = Chief Excel Officer
Wish you all the best!
Thanks.
It is really very good and helpfull!!!
Great Chandoo….
[...] SUMIFS formula [...]
[...] [Related: Introduction to Excel SUMIFS / COUNTIFS Formulas] [...]
Question for you Chandoo, and maybe it is already addressed somewhere in the dialogue above.
Can the “SUMIFS()” formula fully replace the ARRAY formula that looks up criteria in multidimensional ranges? For example, I have the table below, I would like to search by Dept, Employee, Type, and then horizontal for a specific month. I cannot get the horizontal sum-if function to work.
The following formulas return #VALUE!, but of course works when I change the sumrange to equal a specific month column.
=SUMIFS($D$2:$F$5,$A$2:$A$5,”Sales”,$B$2:$B$5,”Mary”,$C$2:$C$5,”Contract”,$D$1:$F$1,”MAR”)
A
B
C
D
E
F
1
Dept
Employee
Type
JAN
FEB
MAR
2
Finance
John
Contract
160
160
160
3
Sales
Bob
Full-time
195
180
185
4
Sales
Mary
Contract
155
150
170
5
Sales
Fred
Full-time
165
155
150
I’d like to replace the cumbersome ARRAY formulas in my worksheets now that I have 2010, but the new SUMIFS formulas still seems to be limited in comparison (although it is much easier to understand).
Hey, great article and illustrations.
This YouTube video is also good to see the sum, sum if and sum ifs functions in action – youtu.be/pYdOukeuENQ?a
Sir….Brilliant tutorial with illustrations….and u r SuperBrilliant.
Its a pleasure to watch ur vids.
Blessing$.