You are here
Using SUMIF to add up cells in Excel that meet certain criteria
The SUM function in Excel allows you to add up the values in a range of cells. However, sometimes you only want to add up the cells that meet certain criteria. That's where the SUMIF function comes in handy, along with the more capable SUMIFS function.
There are two common scenarios for using SUMIF:
- You want to add up all the cells in a range that meet a certain criteria, e.g. all cells in a range (e.g. Sales) that contain a value of $500 or higher.
- You want to add up all the cells in a range where the cells in another range meet a certain criteria, e.g. add up all cells in a column (e.g. Sales) where the cells in another column (e.g. Quantity Sold) is 5 or more.
SUMIF function syntax
The SUMIF function has the following syntax:
=SUMIF(range,criteria,sum_range)
- range is the range of cells you want to add up. It is required for the function to work.
- criteria is the criteria which must be met for a cell to be included in the total. It is also required.
- sum_range is the range of cells that will be added up. It is optional; if you leave it out, Excel will check the criteria against the sum_range. In the two examples above, the first example doesn't need you to provide a sum_range, while the second example does.
One of the tricky things when constructing a SUMIF function is how to present the criteria. Here are some examples to help you:
- To add up all values that equal 500: enter 500 as the criteria. Excel knows you want to match cells with a value of 500.
- To add up all values that are greater than 500: enter ">500" as the criteria. Notice the use of quotation marks around the criteria. You'll get an error if you leave those out.
- To add up all values that are greater than or equal to 500: enter ">=500".
SUMIF in action - adding up all sales that are greater than a certain number.
The following example is a simple way to demonstrate SUMIF in action where we won't include the criteria_range argument. We'll use the first scenario given at the beginning of this lesson.
- Imagine you have a table showing sales for the week.
- You want to find the total of sales for only those days where sales were greater than $500.
-
You enter the SUMIF formula in to a cell at the bottom of the column of sales figures (along with the SUM formula to give you the overall total)
- Row 14 contains the SUMIF function, and the outcome of the SUMIF function in C14.
SUMIF in action - adding up all sales where the sales quantity is greater than a certain number.
Let's extend the example in the table above to only add up sales where the quantity sold was 5 or more. This is the second scenario described at the start of the lesson.
-
After adding a Quantity column, the table above now looks like this:
- The formula in C4 has been changed to reflect the new criteria, and has also included the sum_range. Now, the SUMIF function checks the quantities in column B to see if they match the criteria supplied, and adds the sales value in column C if they do.
SUMIF where the criteria are text values
You can use SUMIF to add up one column where the value in another column matches a text value in another column. This might be useful in the previous example where we wanted to add up all rows where the Day column included "Monday". There is an example of how to do this in the comments below.
When using text criteria, you can also use the * as a wildcard to match only a portion of the text value. There is an example of how do to this in the comments below.
SUMIF where the criteria is in another cell
You can also use a cell reference as your criteria (i.e. the value in another cell) rather than a number as shown in the examples above. Let's assume that cell D5 contains the value we want to use for our criteria. Here are a couple of examples of formulas using D5:
- =SUMIF(B18:B23,D5,C18:C23) - this will use the value in D5 as the criteria, and sum any values that equal the value in D5
- =SUMIF(B18:B23,>D5,C18:C23) - this will NOT work, and Excel will not accept this formula if you type it in.
- =SUMIF(B18:B23,">"&D5,C18:C23) - this will use >D5 as the criteria, i.e. any values greater than the value in D5.
- =SUMIF(B18:B23,">="&D5,C18:C23) - this will use >=D5 as the criteria, i.e. any values greater than or equal to the value in D5.
In the third and fourth examples, we have to play a bit of a trick on Excel to get it to recognise our criteria (remember that the second example doesn't work, which is where most people get stuck).
Because we want to combine text (>) with a cell reference (D5) to get our criteria, we have to join them together into a single string of text, which Excel can then understand. This is called concatenation, which you can learn more about here. That's what you'll see in action in the third and fourth example.
Extending the SUMIF function
The SUMIF function is very useful, but has some limitations. For example:
- What if you only wanted to add up sales amounts where the quantity was 5 or more AND the sales amount was greater than $500? SUMIF won't do the job - you'll need to use SUMIFS for that, which allows you to provide multiple sets of criteria and criteria ranges.
- What if the Sales column was actually a Unit price, and you wanted to add up the total sales. You'd need to multiply the quantity by the sales amount, and then find the total. That's where the SUMPRODUCT() function comes in.
What do you think?
I'd love to hear your feedback about this lesson. Did it help? Is it accurate? Do you have a particular problem you want to solve and you can't quite get the SUMIF function to work for you? Why not add a comment or ask a question about your specific scenario below.
Want to learn more? Try these lessons:
Our Comment Policy.
We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours. One other point to note - if you post a link in your comment, it will automatically be deleted.
Comments on this lesson
sumif maybe
I have a spreadsheet with columns I Actual year to date, j budget year to date and 12 columns M to X with each months budget
Is there a way I can fill column j with the sum of correct rows ie m period 1, m and n period2, m,n,o period 3 etc
I had thought of adding a month no at the top of each column so that if col j row1 had 2 then col j row 5 would add m5+n5 row 6 would add m6+n6 and then increase as i change the month no at the top of row j
Any easy way to do this ???
Thanks
Ricky
sumif function
i want to use sumif function, where i use specific text as criteria in criteria range .
example: use "13ccl01" as criteria
in criteria range cell
CRITERIA RANGE SUM range
13ccl01f01 345
13ccl01f02 234
13ccl01fo3 234
13ccl02f01 345
13ccl02f02 43
13ccl03f01 14
if it is not possible by sumif, then give me another solution
SUMIF using text criteria and wildcards
Hi Sajib
This is actually pretty easy to do using * as a wildcard in your criteria:
=SUMIF(CRITERIA_RANGE,"*12ccl01*",SUM_RANGE)
Here's a picture:
Sumifs with contains
=SUMIFS(H$12:H$101,$F$12:$F$101,$F118)
H-range is the column to sum
F-range is the column to check criteria in (and may contain mulitiple values ie: a, b, c, etc)
F118 is the cell reference to the actual single value for criteria (ie: a)
so I want to sum up all value in H-range if F-range contains F118
Can that be done?
Sumifs with contains
I should clarify that F-Range (in each cell of range) contains multiple values...so the contains on F118 would be to target each cell in F-range.
Sumif( with a cell reference containing text for criteria - See
I am not sure if this is correct way to ask a question, but my question is can I use a cell reference containing text as the criteria in a sumif( function
ie sumif(range.....,B5,range......)
Where B5 may be "Mary" and where there are multiple entries of Mary and I wish to sum the values next to her name
If not a proper way to ask advise, please accept my appology
Yes, you can do this in Excel
Hi
First of all, this is exactly the right place to ask a question. Happy to help!
In answer to your main question, yes you can do what you're asking. By putting your criteria value into a cell, you can then reference that cell when you write your SUMIF formula.
I've created a simple example to illustrate how this might work.
I've included column C in the picture because it includes a copy of the formula I used in cell B2.
Essentially, this formula references cell B1 to find the criteria for the SUMIF. In this case it's using Mary. However, I've designed this spreadsheet so you can type any one of the names from the list and the correct sales figure will be calculated. The name in cell A2 will also adjust to reflect the name in cell B1.
You can download a copy of this spreadsheet here.
One final comment ... you should check out the lesson on PivotTables. A PivotTable would be perfect in this scenario for creating a table showing all sales people and the total sales they have made.
Please let me know if you need any more help.
Regards
David
partial text from a cell as criteria
Hi there,
These last few questions get REALLY close to what I'm trying to do. Using this particular example, let's say you wanted to find the total sales of every salesperson whose name starts with M. Is it possible to refer to a cell that has a full name (say, Mary) to get the "M", then use that "M" as the criterion for summing?
I realize the wildcard * function would work for this, if I just put "M" in a cell and used that; unfortunately I'm using larger identifiers, and lots of them--I'd love to be able to refer to a cell that has part of the RANGE text somehow. The real data would look something like this:
RANGE
100214011001a
1004930311001a
101043832001a
101596432001a
101596432001b
CRITERIA
101596432001
SUM RANGE
115304
42273
48167
329977
83918
99311
Thanks for any help you can offer!
Liz
Try SUMPRODUCT as part of an Array formula
Hi Liz
Great question .... here's a potential answer for you, although you may need to modify it to suit your needs.
Using my example above, the following formula will find the total for all sales people whose names begin with M:
=SUMPRODUCT(--(LEFT(A6:A16,1)="M"),B6:B16)
You can enter it into the example spreadsheet referenced in my previous comment above. However, the BIG thing to note is that this is an array formula. The LEFT function does not normally allow you to use a range as the first argument (A6:A16), unless you enter the formula as an array.
To do this, click on the cell that should contain the result, enter the formula, then press CTRL+SHIFT+ENTER. When the formula is saved into the cell, it will appear in the formula bar like this:
{=SUMPRODUCT(--(LEFT(A6:A16,1)="M"),B6:B16)}
The {} indicate that this is an array formula. When you edit the formula the {} will disappear; you'll need to remember to press CTRL+SHIFT+ENTER again once you've finished editing it.
In this example, the formula uses the LEFT function to find the first letter in each row in column A and compare it to "M". If it matches, it will return 1. If it doesn't, it will return 0.
The -- in front of the LEFT function is a trick we need to play on Excel to force it to treat the result from the LEFT function as a number rather than a text value. It does this by turning it into a negative number then back again (that's what the -- does). The only way Excel can do this is convert the text value into a number.
The SUMPRODUCT function then multiplies that result by the value in column B for that row and adds up these results for all rows specified in the range (A6:A10). Obviously if there was no match in column A, then the result for that row will be 0, otherwise it will be the value in column B.
So, from here you could modify the LEFT function to return different values to match different criteria. For example, this version of the function would allow you to use a cell (A3 in this case) instead of hardcoding "M" into the function:
{=SUMPRODUCT(--(LEFT(A6:A16,1)=A3),B6:B16)}
The problem with this example is that it will fail if you enter a value into A3 that is longer than 1 character. You could get around this using the LEN function like this:
{=SUMPRODUCT(--(LEFT(A6:A16,LEN(A3))=A3),B6:B16)}
This version of the formula looks at the length of the text string in A3 and takes that many characters from the cells in A6.
Hopefully this gives you enough to work with - post back once you've tried it out and let me know how you got on.
Regards
David
Thanks for the quick reply,
Thanks for the quick reply, David!
This solution is interesting. I like it, but I'm having trouble getting it to work with my numbers--they're alphanumeric, not sure if that's the problem? There's an error that reads, "The formula contains unrecognized text."
That said, I did find a solution: shifting my search range over to a column in which I had removed the final character from each row of the original search range, using left(cell, len(cell)-1). This way, if I have two IDs that are identical but for a final -a or -b at the end, the new column just shows the same ID twice; and then, the SUMIF function finds both and sums them. Less elegant, but it works.
-liz
Hi Liz
Hi Liz
Aha - yes, the LEN function assumes it's looking at a text value, not a number.
Try this formula instead, making sure to press CTRL+SHIFT+ENTER to make it an array formula:
=SUMPRODUCT(--(LEFT(TEXT(A5:A15,"#"),LEN(TEXT(A18,"#")))=TEXT(A18,"#")),B5:B15)
This version uses the TEXT function to convert the numbers in column A to an unformatted string, which means that LEN is able to function correctly.
David
yes!
That does it! Thanks, that's great to know. So helpful.
Best,
liz
using this example, but looking for a different answer
So using the example above I've gotten very close to where I need to be except I am looking for multiple criteria. So what If I want to add Mary AND Olga? How do I put the second criteria in? I've tried sumifs but cant seem to get it right. Using the example below I'd like to know how many entire galleries I've sold so I would need criteria b2, b4 and b5.
A B
1 6 activity clock with home collection
2 1 activity clock with entire gallery
3 1 routine teacher with school collection
4 3 routine teacher with entire gallery
5 7 Entire gallery
6 4 Routine teacher with home collection
7 3 home collections
etc
The other way to do it might be to make the criteria a "text". Like any cell that contains "entire gallery". Whichever one is easier to get my spreadsheet to say I sold Entire galleries. Any help you can provide would be great. I have already gotten so much further than I would have because of your guidance.
Kelly
how to use sumifs
sumifs you have dates with multiple group related to a total hours and I want a particular group sum by a particular date.
sumifs (on 6-5-13) = Leila
how can I write a formula for that
compare text value
Hi, how should I do next: One person is doing different roles i.e. A,B,C,D... when he is working as "A" I need to sum that as 1 so if A,A,B,C,A,C,B value should be 3 ( 3 times as "A")?
Thank you in advance for help.
SUMIF FORMULA
Hey I have updated my sheet with the same data as mentioned above and also filled the B1 celll with the formula you have showed, but it is showing the value as 0 (zero). It is not working for me. Is there any setting to be done or what may be the reason, please let me know
SUMIF on the condition of text in another cell
Hi, I don't think this is answering the question that was asked. I believe I have the same question, if I am not mistaken.
Lets say the spreadsheet is evaluating sales per employee throughout the day:
A B
1 $45 Mary
2 $60 Ronald
3 $100 Terrance
4 $78 Mary
5 $5 Mary
6 $51 Terrance
7 $105 Ronald
8
9 Total of
Mary's
Sales
10
11
n
What formula should be written in A9 to add together only the values in A1 to A7 where the text in Column B says "Mary"?
In other words, how do we make a formula that adds together only Mary's sales: $45, $78, $5
If that is not what the original poster was asking, I would still like to know. Thank you!
Correction to the example
Sorry, the letter A in the pretend spreadsheet I wrote above should be sitting above the $dollar amounts column, and the B should be sitting above the employee names column. Oops
When I use this formula I get
When I use this formula I get an error saying argument must be range. Any idea what would be causing this
This is perfect... but...
This Sumif worked perfectly for me, but is it possible to add one more caveat: for the sum to only work within today to 7 days ago? Using the same table as above would give me enough to figure out on my own sheet.
Thank you anyone who can help!
sumif not matching text cells when it leads with a 0
I have column A and column J as text columns, and any cell that starts with a ZERO, somehow does a partial match. for instance, this 000101 matches 00101. When using =SUMIF($A$11:$A$500000,J11,$E$11:$E$500000)
J11 is the 000101, yet it is summing up cells in A:A that are only 00101.
SUMIF Functions with Text
I'm building a CRM in Excel and want to keep track of where my company prospects are coming from (Walk In, Cold Calls, etc.). I have a column named source and each row represents a client. I'm trying to create a chart that will show which source is proving to be the most effective.
For the chart, I know I have to create a separate set of cells with a numerical total. What I want is for that total to be automatically calculated by using, what I suppose, is a SUMIF function. However, I cannot figure it out. In other words, for my "Walk In" total, I'd like that numerical cell to be updated whenever I had a new client to the column that shows the source of the prospect.
To simplify my question: Column 'E' holds all the different sources. Cell H3 will hold the total # of 'Walk In' prospects I have, to be calculated by how many times I have "Walk In' in Column 'E'. If there are 9 cells with 'Walk In' in Column 'E', I'd like for cell H3 to show that by a function.
Thanks for any help/advice you can give!
Disregard!
COUNTIF would be the right function to use. I'm such an idiot. Ha!
Sumif
Is there a way to use sumif when I want to add only specific cells in a column (not a range)? When I reference the cells in the sumif formula it tells me I have too many arguments? For example =sumif(a2,a3,a5,a6,false,b2,b3,b5,b6). Notice I skip cells a4 and b4. What would I use instead of commas. It thinks I'm making another argument.
Thanks,
Shelly
ref to your question
SUMIF uses two columns
1 is the item you are basing your sum on (what you want to match x to x to)
2 what you are summing up if there is a match
Using SumIf to always add to specific sum
Hi there,
I'm hoping this is the right equation to achieve this, but basically what I'm trying to do is have cells automatically readjust to come up with the same sum, if one of the cells is changed. for instance, if i have
4 5 1 10
I should also be able to change the 4 to a 5 and the one will change to 0, so it still equals 10.
Is there a way to do this?
Using SumIF by Referencing To Another Cell as Criteria
I know this is really simple, but I cannot seem to figure this out. I want to add up cells that are greater than an input cell that I can change. My formula reads: =SUMIF(A2:A76, >=N3, B2:B76)
However, this formula gets an error for the criteria, but if I type =SUMIF(A2:A76, ">90", B2:B76) for the criteria it works, but I want to be able to reference a cell as the criteria. So, that I could just change that input cell's value and alter the output of the function.
SUMIF - Use concatenation to reference another cell as criteria
Hi Cy
It's simple, yet for some reason it's not.
Excel expects the criteria argument in the formula to be a text string. You can do this using concatenation, which basically means joining text and number values into a text string.
Here's the correct version of your formula:
=SUMIF(A2:A76, ">="&N3, B2:B76)
Regards
David
Thanks David!!
Thanks David!!
formula to add value n if the date is m to cell O
hello I need help to build the folliwing, I need to add numbers in to one cell depending on a cells value and the specific month, example, I made purchases for a cost center 211 on September 12, for a total of 5, then another for 10 for same Cost center on sep 20, how can I have this to be sum in to one cell for my actual spends, hope to make sense.
Adding up sales by month and cost centre
Hi Chuck
One option is to create another column in your spreadsheet next to the date field and use the Month() function to determine the month for the date in the cell next to it, e.g.:
=MONTH(A2)
Note that the MONTH function returns the number of the month, e.g. 9 for September, 10 for October etc. It doesn't distinguish between different years, so the MONTH function would return 9 for both September 2012 and September 2013.
If you have the following columns you could then use the SUMIFS function to calculate what you're after:
- A2:A10 contains the date
- B2:B10 contains the month number
- C2:C10 contains the cost center number
- D2:D10 contains the purchase value
The function would look like this:
=SUMIFS(D2:D10,B2:B10,9,C2:C10,201)
Note that I've used 9 as the third argument since I want to add up purchases in September. 201 is the cost center that I want to find the sum for.
You can read more about SUMIFS here. (opens in a new window)
The other option is to use a Pivot Table. This would quickly give you a report that showed purchases for each cost centre, broken down by month. You could just easily get a report by month broken down by cost centre. The advantage of a Pivot Table is that you wouldn't need to add a separate column to calculate the month. You can read how to create a pivot table here, and read the lesson on how to group dates in a pivot table by month here.
Finally, this article on another site shows how to use SUM in an array formula to do something similar to what you are trying to do. I've tried applying it to your requirement without success (so far) but if I find a solution I'll let you know.
Regards
David
- See more at: //fiveminutelessons.com/learn-microsoft-excel/using-sumif-add-cells...
Sumif
I have a spreadsheet with columns representing the days of the week. Rows representing employees. In each cell we will enter the daily hours for each contractor we work for. We have two rates we use. I would like a formula that will add up the hours from each row based on the rate we charge. Thanks for any help
Hi Cory
Hi Cory
If this was my spreadsheet, I'd have an additional column that has the rate for each employee.
Thanks for the info. Last question, How would I write out a formula that would do what you described, but only add the sales amounts from days that were <8 and >5?