Sumif and Countif Formulas in Excel
- 8 Comment
There are many times when analyzing large amounts of data when you might want to sum or count a column or range based on certain criteria. One example is if you have a database of products you have sold, you might want to know the sum of profits for a particular product - which is where the sumif formula would come in handy. Or possibly for the same example you want to know how many instances of a particular item you have sold, which is countif’s formula specialty.
Let’s start by taking a look at the formulas:
COUNTIF(range,criteria)
SUMIF(range,criteria,sum_range)
Both the Countif and Sumif formulas have very similar criteria. When you are using the Countif formula you need to the it the range of cells that you want evaluated with a certain criteria. Sumif is much the same way with one exception, the sum_range can be different from the range you are evaluating with a specific criteria. We well show numerous examples below to illustrate how the formulas work and their differences.
Examples
Example 1: =COUNTIF(C2:C8,C2) will return 3. In this example, we are telling excel to count the number of times orders were placed for “Red” products - 3 instances were found in lines 2, 7 and 8.
Example 2 : =COUNTIF(D2:D8,”>10″) will return 3. In this example, we are telling excel to count the number of times orders were placed for that were greater than $10 (notice the item criteria needed to be placed in quotes this time since it was not a cell referent) - 3 instances were found in lines 4, 7 and 8.
Example 3 : =SUMIF(A2:A8,111,D2:D8) will return $14. In this example, we are telling excel to sum the prices (column D) of the any product in column A that equal “111″ - 2 instances were found in rows 2 and 5 which summed values $10 and $4.
Future Considerations
One of the next sections we will be adding to ExcelHints.com is there may come a time when you want to sum a range based on more than one criteria. In these cases you want to use the Ctrl-Shift-Enter (or CSE) formulas which use arrays to calculate. Because these get more involved than the straight forward Countif and Sumif formulas, we will save them for their own topic.
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.
Related Hints
8 Comments on this post
Trackbacks
-
Mils said:
I work with the sales team and they use a pieplien tracker to track all sales. The biggest problems is they’re not Excel prficient and when l get the spreadsheets back the fomatting and formulas are a mess.
Question - Is there any way that l can fix the formatting in the spreadsheet without anyone changing it e.g. l want a black grid with headings with a blue baclground but if someone wanted to dlete of change it they can’t. I don’t want to password protect the cells as they need to enter data into them.
Thanks for your help!
October 11th, 2008 at 6:03 am -
john said:
Hi Mils - Great question.
It might be slightly different depending on what version of Excel you are using. Give this a shot though.
Select all the cells you want to be able to edit the numbers for, select Format Cells and find the tab or section where you can uncheck “Locked” for the cell protection. Make all the formatting changes you want to make and then protect the sheet. By default, Excel will only allow users to edit the cell values and not the formatting.
In Excel 2007, I know there are options where you can allow users to also edit the formatting, but that is not the default setting.
Hopefully that answers your question and thanks for stopping by Excel Hints!
John
October 11th, 2008 at 10:19 am -
john said:
I’m actually going to probably right a post about this soon, because it was a good question that many others might find useful.
October 11th, 2008 at 10:20 am -
Mils said:
Hi,
Re:Vlookups/ Hlookups. I have a calendarized spreadsheet that goes from Jan to Feb and in each cell l’m pulling data through from a data sheet with H/Vlookups for each respective month.
Issue: The problem is when l’ve got the formula working fine in January and l want to copy and paste it through to December, how can l do this without manually going into each month and changing the column reference i.e. let say January vlookup is looking at column 2 in the master data sheet then Feb should pull through coulmn 3 and March column 4 and so on. Usually what happens is that all months will reference column 2 only.
Thanks for your help.
October 13th, 2008 at 12:34 pm -
P K Gambhir said:
Very useful for excel user community. A good job. Keep it up.
November 18th, 2008 at 2:26 am -
john said:
Hi PK - thanks for the encouragement. I’m glad you are finding the site useful.
November 18th, 2008 at 11:41 am
Loading ...
[...] ways built in already to count that range for you. One way we already explored is through the CountIf Formula, and also the Count Formula. Excel also features the CountA and CountBlank Formulas to make your [...]
[...] have already shown you how to use the formula in the SumIf and CountIf Foumulas post. But here I just wanted to mention this is an example where you can use [...]