![\"SUMIF,](\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/feature-sumifs-countifs-functions.png\")
<\/div>\nThe SUMIF<\/strong> and COUNTIF<\/strong> functions allow you to conditionally sum or count cells based on a single condition<\/b>, and are compatible with almost all versions of Excel:<\/p>\n The SUMIFS<\/strong> and COUNTIFS<\/strong> functions allow you to use multiple criteria<\/b>, but are only available beginning with Excel 2007:<\/p>\n AVERAGEIF and AVERAGEIFS are also part of this family of functions and have the same syntax as SUMIF and SUMIFS.<\/p>\n This Article (bookmarks):<\/p>\n We'll start off by using a product sales table to demonstrate a few different SUMIF and COUNTIF formulas. I've listed a few of these examples below. To see these formulas in action and try them out yourself, you can download the example file below:<\/p>\n <\/span>Download the Example File<\/b><\/a> (SumIf-CountIf.xlsx)<\/p>\n NOTE<\/span> This formula will also match \"Student\" because the SUMIF family of functions are not case-sensitive<\/b>. You can use wildcard characters<\/b> within the text string, such as \"?s*\"<\/b> to match values where the second letter is s.<\/p>\n<\/div>\n NOTE<\/span> When using numeric criteria, COUNTIF and SUMIF ignore text values. Numeric date values can be an exception to that (see below for date comparisons).<\/p>\n<\/div>\n NOTE<\/span> This technique is useful when you want to allow the user to choose or enter different criteria or search strings.<\/p>\n The cell K9 could contain a value like \"student\" or \"40\" or a criteria string such as \">40\" or \"<>student\"<\/p>\n<\/div>\n NOTE<\/span> The SUMIFS, COUNTIFS, and AVERAGEIFS functions are used for multiple AND conditions. Formulas for OR conditions are a bit more tricky (see below).<\/div>\n When using dates as criteria for the COUNTIF and SUMIF functions, Excel does some interesting things, depending on whether you are using \"=\" or \"<\" as the criteria and whether the dates in the criteria range are stored as numeric date values or text values.<\/p>\n Remember<\/b>: Date values are stored in Excel as sequential numbers starting with 1 for 1\/1\/1900. The cell formatting may display the date in different ways, but COUNTIF and SUMIF comparisons are based on the value stored in the cell, not the way a cell is formatted. That is a good, because we normally want to compare dates and numbers without having to worry about how they are formatted.<\/p>\n When using criteria such as \"=3\/1\/17\" or \"Mar 1, 2017\", Excel will recognize the criteria as a date and will count all date values<\/i> in the criteria_range<\/i> matching that date. Excel will ALSO count recognized dates stored as text values<\/i> in the criteria_range<\/i>, such as \"March 1, 2017\" and \"3\/1\/2017\" (but not \"March 1st, 2017\" because Excel doesn't recognize it as a date).<\/p>\n When using <, >, <=, or >=, Excel still recognizes the criteria as a date, but it does not<\/b> convert text values in the criteria_range<\/i> to date values.<\/p>\n You can use the TODAY function to make comparisons based on the current date, like this:<\/p>\n SUMIFS is very useful in account registers<\/a>, budgeting, and money management<\/a> spreadsheets for summarizing expenses by category and between two dates.<\/p>\n The SUMIFS example below sums the Amount column with 3 criteria: (1) the Category matches \"Fuel\", (2) the Date is greater than or equal to the start date, and (3) the Date is less than or equal to the end date.<\/p>\n The following screenshot shows an example from the download file:<\/p>\n COUNTIFS and SUMIFS can easily handle a condition such as 1 < x < 4 (which means x > 1 AND x < 4). However, if you are trying to make a spreadsheet compatible with older versions of Excel, you can use COUNTIF or SUMIF by subtracting the results of the condition x <= 1 from the results of the condition x < 4.<\/p>\n A common need for these formulas is to sum values between two dates. Remember that date values are stored as numbers.<\/p>\n COUNTIFS and SUMIFS handle multiple AND conditions, but OR conditions such as X<2 OR X>3 are usually easier to handle by evaluating each condition separately and then adding the results. The two formulas below do essentially the same thing.<\/p>\n To avoid double-counting cells, the conditions must not overlap. For example, the condition \"=*e*\" would overlap with the condition \"=yes\". The condition \"<40\" would overlap with the condition \">20\". If the conditions overlap, you may end up counting or adding a value twice. If there is a possibility of conditions overlapping, then you may need to use a SUMPRODUCT formula as explained below.<\/p>\n The key to avoid double-counting is to recognize that FALSE+FALSE=0 and TRUE+FALSE=1 and TRUE+TRUE=2. This means that for a logical OR condition to be true, we can check whether the sum of two or more conditions is > 0.<\/p>\n The SUMIF family does not have a case-sensitive option, so we need to resort back to using array formulas or SUMPRODUCT. The FIND and EXACT functions both provide a way to do case-sensitive matches.<\/p>\n MAXIFS<\/strong> and MINIFS<\/strong> are new Excel functions available in the latest releases (Excel for Microsoft 365, Excel 2019). Their syntax is similar to SUMIFS, allowing you to use multiple criteria.<\/p>\n Older versions of Excel do not have the MAXIFS or MINIFS functions, but you can use an array formula<\/a> like this (remember to press Ctrl+Shift+Enter):<\/p>\n=SUMIF<\/span>(criteria_range<\/span>, criteria<\/span><\/span>, sum_range<\/span>)<\/pre>\n=COUNTIF<\/span>(criteria_range<\/span>, criteria<\/span><\/span>)<\/pre>\n=SUMIFS<\/span>(sum_range<\/span>, criteria_range1<\/span>, criteria1<\/span><\/span>, criteria_range2<\/span>, criteria2<\/span><\/span>,...)<\/pre>\n=COUNTIFS<\/span>(criteria_range1<\/span>, criteria1<\/span><\/span>, criteria_range2<\/span>, criteria2<\/span><\/span>,...)<\/pre>\n\n
1) SUMIF and COUNTIF Examples<\/h2>\n
![\"Sales](\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/sales-table-for-demonstrating-sumif-countif.png\")
<\/p>\nCriteria are Text Values<\/h3>\n
Example: Sum of Sales where Category equals \"student\"<\/h4>\n
=SUMIF<\/span>(category_range<\/span>,\"student\"<\/span>,sales_range<\/span>)<\/pre>\nNot Equal To (<>)<\/h3>\n
Example: Sum of Sales where Model is NOT equal to \"B\"<\/h4>\n
=SUMIF<\/span>(model_range<\/span>,\"<>B\"<\/span>,sales_range<\/span>)<\/pre>\nExample: Sum of Sales where Category does NOT contain the letter \"u\"<\/h4>\n
=SUMIF<\/span>(category_range<\/span>,\"<>*u*\"<\/span>,sales_range<\/span>)<\/pre>\nCriteria is an Alphabetical Text Comparison<\/h3>\n
Example: Sum of Sales where Model is less than \"C\"<\/h4>\n
=SUMIF<\/span>(model_range<\/span>,\"<C\"<\/span>,sales_range<\/span>)<\/pre>\nCriteria is a Numeric Comparison<\/h3>\n
Example: Number of products priced over $40<\/h4>\n
=COUNTIF<\/span>(price_range<\/span>,\">40\"<\/span>)<\/pre>\nCriteria matches Blank Cells or Empty Cells<\/h3>\n
Example: Count of products where On Sale is blank<\/h4>\n
=COUNTIF<\/span>(on_sale_range<\/span>,\"\"<\/span>)<\/pre>\nCriteria matches Non-Blank Cells<\/h3>\n
Example: Number of products on sale (where On Sale is not blank)<\/h4>\n
=COUNTIF<\/span>(on_sale_range<\/span>,\"<>\"<\/span>)<\/pre>\nCriteria includes a Cell Reference<\/h3>\n
Example: Sum of Sales where Price is greater than the value in cell H3<\/h4>\n
=SUMIF<\/span>(price_range<\/span>,\">\"&H3<\/span>,sales_range<\/span>)<\/pre>\nCriteria is In Another Cell<\/h3>\n
Example: Sum of Sales using the criteria defined in cell K9<\/h4>\n
=SUMIF<\/span>(criteria_range<\/span>,K9<\/span>,sales_range<\/span>)<\/pre>\nMultiple Criteria<\/h3>\n
Example: Sum of Sales where Category = \"student\" AND<\/b> Price > 30<\/h4>\n
=SUMIFS<\/span>(sales_range<\/span>,category_range<\/span>,\"student\"<\/span>,price_range<\/span>,\">30\"<\/span>)<\/pre>\nExample: SUMIFS between two dates<\/h4>\n
=SUMIFS<\/span>(sum_range<\/span>,date_range<\/span>,\">=1\/1\/2017\"<\/span>,date_range<\/span>,\"<1\/31\/2017\"<\/span>)<\/pre>\n2) Using Dates as Criteria<\/h2>\n
Criteria is a Date<\/h3>\n
=COUNTIF<\/span>(criteria_range<\/span>,\"=3\/1\/17\"<\/span>)<\/pre>\nCriteria is Greater or Less than a Date<\/h3>\n
=COUNTIF<\/span>(criteria_range<\/span>,\">3\/1\/17\"<\/span>)<\/pre>\nComparison to TODAY<\/h3>\n
=SUMIF<\/span>(date_range<\/span>,\"<\"&TODAY()<\/span>,sum_range<\/span>)<\/pre>\n3) SUMIFS Example: Income and Expense Report<\/h2>\n
=SUMIFS<\/span>(amount_range<\/span>, category_range<\/span>, \"Fuel\"<\/span>, date_range<\/span>, \">=1\/1\/2018\"<\/span>, date_range<\/span>, \"<=1\/31\/2018\"<\/span>)<\/pre>\n![\"SUMIFS](\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-formulas\/sumifs-example-income-and-expense-report.png\")
<\/p>\n4) SUMIF and COUNTIF Between Two Numbers (1 < x < 4)<\/h2>\n
\n
\n Condition<\/th>\n Formula using COUNTIFS<\/th>\n<\/tr>\n \n 1 < x < 4<\/td>\n =COUNTIFS(range<\/i>,\">1\",range<\/i>,\"<4\")<\/td>\n<\/tr>\n \n Condition<\/th>\n Formula using only COUNTIF<\/th>\n<\/tr>\n \n 1 < x < 4<\/td>\n =COUNTIF(range<\/i>,\"<4\") - COUNTIF(range<\/i>,\"<=1\")<\/td>\n<\/tr>\n \n 1 <= x < 4<\/td>\n =COUNTIF(range<\/i>,\"<4\") - COUNTIF(range<\/i>,\"<1\")<\/td>\n<\/tr>\n \n 1 < x <= 4<\/td>\n =COUNTIF(range<\/i>,\"<=4\") - COUNTIF(range<\/i>,\"<=1\")<\/td>\n<\/tr>\n \n 1 <= x <= 4<\/td>\n =COUNTIF(range<\/i>,\"<=4\") - COUNTIF(range<\/i>,\"<1\")<\/td>\n<\/tr>\n<\/table>\n Example: SUMIF between two dates ( 1\/1\/2017 <= date <= 1\/31\/2017 )<\/h3>\n
=SUMIF<\/span>(sum_range<\/span>,date_range<\/span>,\"<=1\/31\/2017\"<\/span>)-SUMIF<\/span>(sum_range<\/span>,date_range<\/span>,\"<1\/1\/2017\"<\/span>)<\/pre>\n5) SUMIF and COUNTIF with OR Conditions<\/h2>\n
\n
\n Condition<\/th>\n Formula<\/th>\n<\/tr>\n \n x < 2 or 3 < x<\/td>\n =COUNTIF(range<\/i>,\"<2\") +<\/b> COUNTIF(range<\/i>,\">3\")<\/td>\n<\/tr>\n \n x < 2 or 3 < x<\/td>\n =SUM(COUNTIF(range<\/i>,{\"<2\",\">3\"}))<\/td>\n<\/tr>\n<\/table>\n Use SUMPRODUCT for overlapping OR conditions<\/h3>\n
Sum of Sales where Model is equal to A or B.<\/h4>\n
=SUMPRODUCT<\/span>(sales<\/span>,1*( ((model<\/span>=\"A\")+(model<\/span>=\"B\"))>0 ))<\/pre>\nSum of Sales where Model = \"A\" or Price > 45<\/h4>\n
=SUMPRODUCT<\/span>(sales<\/span>,1*( ((model<\/span>=\"A\")+(price<\/span>>45))>0 ))<\/pre>\n6) Case-Sensitive SUMIF and COUNTIF<\/h2>\n
Sum of Sales where Category exactly matches \"student\" (case-sensitive)<\/h3>\n
=SUMPRODUCT<\/span>(sales<\/span>,1*EXACT<\/span>(category<\/span>,\"student\"<\/span>))<\/pre>\nSum of Sales where Category contains \"Stu\" (case-sensitive)<\/h3>\n
=SUMPRODUCT<\/span>(sales<\/span>,1*ISNUMBER<\/span>(FIND<\/span>(\"Stu\"<\/span>,category<\/span>)))<\/pre>\n7) MAXIF or MINIF Formula<\/h2>\n