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. 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,sumrange). 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. sumrange 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 sumrange.

In the two examples above, the first example doesn't need you to provide a sumrange, 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 criteriarange 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 sumrange. 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'. When using text criteria, you can also use the. as a wildcard to match only a portion of the text value.

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. 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 -. 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. What do you think?

I'd love to hear your feedback about this lesson. 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. 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. 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. Submitted by Visitor on Sun, - 01:11. 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. One final comment. You should check out the. 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. 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 01a 001a 01a 01a 01b CRITERIA 01 SUM RANGE 13 77 1 Thanks for any help you can offer! 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. 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'.

Adding Blanks Rows Above Specific Text In Excel For Mac Mac

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. Submitted by Leila Smith on Mon, - 18:55. 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.

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

(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, and read the lesson on here. Finally, 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:. Submitted by Cory on Mon, - 22:11. Hi Cory If this was my spreadsheet, I'd have an additional column that has the rate for each employee.

Let's say you have these columns:. Column A has the name of the employee. Column B has the rate for that employee. Columns C-I have the day of the week (i.e. The hours worked by that employee.

Column J has the sum of columns C-I, which will be the total hours for the week. Column K has the amount each employee is to be paid In column J, I'd have this formula to calculate the hours worked for the employee in row 3: =SUM(C3:I3) In column K, I'd have this formula to calculate the amount due to that employee: =J3.B3 I might also consider combining columns J and K into one with this formula: =SUM(C3:I3).B3 Hopefully that helps? Submitted by cindy on Wed, - 06:25. Hi David, not excel savvy at all refereeing to the following extract out of my spread sheet StockCode Description AbcClass MtdQtySold 12 mnt AveQtysold 3 mnt AveQtysold QtyOnHand QtyOnOrder Supplier Warehouse MinimumQty REORDER QTY 300651 90MM BASKET STRAINER WASTE 0 3 0 295 0 FRANKE JB 0 -295 300651 90MM BASKET STRAINER WASTE 0 8 3 37 0 FRANKE CT 1 -36 I have created this spreadsheet using ODBC( we are using syspro) but the fields it pulls from is warehouses. I would like this sheet to show the total all values per stock item, not per warehouse. Will sum if work here regards Cindy. Hi Cindy My guess is that you'd be better off using a Pivot Table to do this.

I'm assuming your spreadsheet has lots of rows, lots of stock codes, and multiple warehouses. Keeping track of all of those with SUMIF formulas is going to be a nightmare! A Pivot Table will help you make sense of this data much more easily, and will give you a lot of options for slicing, dicing and reporting your data in different ways. You can (opens in a new tab).

They will all be relevant to what you're trying to do. You can use the techniques covered in the lesson to group your data by Warehouse rather than by Date (although Pivot Tables will let you do both at the same time!). Regards David. Submitted by Tim on Tue, - 20:25. Hi Tim Sounds like you simply want to add up the number of names in the column.

Try one of the COUNT functions:. COUNT will count the number of cells in a range. COUNTA will count the non-blank cells in a range.

COUNTBLANK will count the blank cells in a range. COUNTIF will count the number of cells in a range that meet the criteria you specify. COUNTIFS extends COUNTIF to support multiple criteria.

You can, and I've got the other functions on my 'lessons to write list', although they are fairly straightforward. Regards David. Submitted by MAGGIE on Thu, - 11:43. I am using a SUMIFS formula to bring back actual spend for contracts and purchase orders. Here is my current formula: =SUMIFS(COGNOS!$AN$2:$AN$10000,COGNOS!$AG$2:$AG$10000,D7,COGNOS!$AH$2:$AH$10000,E7,COGNOS!$AI$2:$AI$10000,F7) My 'sum range' is in one column on my COGNOS tab (COGNOS!$AN$2:$AN$10000) which is a dump of all actual spend from our reporting system.

My 'criteriarange1' is in another column on my COGNOS tab (COGNOS!$AG$2:$AG$10000) which looks to the contract number in the dump of actual spend. My 'critera1' is on my project reporting tab which has a list of the contracts and purchase orders so it looks to cell D7 on that sheet (column D being contract number). My 'criteriarange2' is in another column on my COGNOS tab (COGNOS!$AH$2:$AH$10000) which looks to the contract release number in the dump of actual spend.

My 'criteria2' is on my project reporting tab and looks to cell E7 on that sheet (column E being contract release). This part of the formula is working fine.

I want it to return the sum of actuals based on the contract number AND release number. However, I need to figure out how say OR this PO #. So it is a contract or PO on each line in my project report. So it will look to 'criteriarange3' (COGNOS!$AI$2:$AI$10000) which is PO # in the dump of actuals and then look to see if there is a match on the project report tab which is column F. How can I tell the formula to look for contract & release OR PO# in the three different columns?

If you need more information, please let me know. Thanks so much for any help!!!!! I have a spreadsheet to track contracts. Within my spreadsheet I am trying to sum the costs of contracts if one of 3 columns is marked yes (the columns indicate specific criteria about the contract holder and I simply put a Y in the column if the attribute is appropriate). The problem is, for any given contract the there could be marks in any one to all three of the columns. The formuals I have been able to figure so far, will add the contract mulitple times if mulitple colums are marked for each contract.

Any suggestions?

Adding Blanks Row Above Specific Text In Excel For Macro Insert

= (D5 = ', 'Open', 'Closed' ) How this formula works The logical expression =' means 'is empty'. In the example shown, column D contains a date if a task has been completed. In column E, a formula checks for blank cells in column D. If a cell is blank, the result is a status of 'Open'. If the cell contains value (a date in this case, but it could be any value) the formula returns 'Closed'. The effect of showing 'Closed' in light gray is accomplished.

Adding Blanks Rows Above Specific Text In Excel For Mac 2017

Display nothing if cell is blank To display nothing if a cell is blank, you can replace the 'value if false' argument in the IF function with an empty string (') like this.