Counting formulas
Count rows that match one condition or several, between dates, by text, blanks and unique values. 80 answers, each checked by running it, with a live example and an exercise.
How to count cells between two dates in Excel
=COUNTIFS(B2:B11,">="&G1,B2:B11,"<="&G2)Use COUNTIFS with two conditions on the same date column: on or after the start date, and on or before the end date.
How to count cells that contain specific text in Excel
=COUNTIF(A2:A10,"*"&D1&"*")Use COUNTIF with wildcards: =COUNTIF(range,"*"&text&"*") counts every cell that contains the text anywhere.
How to count with multiple criteria in Excel
=COUNTIFS(B2:B10,G1,C2:C10,">="&G2,D2:D10,"Won")Use COUNTIFS with one range-and-condition pair per criterion; a row is counted only when all of them are true.
How to count unique values in Excel
=COUNTA(UNIQUE(A2:A10))In Excel 365, wrap UNIQUE in COUNTA: =COUNTA(UNIQUE(range)). In older versions, use =SUMPRODUCT(1/COUNTIF(range,range)).
How to count cells that are not blank in Excel
=COUNTA(C2:C8)Use =COUNTA(range). It counts every cell that has anything in it: numbers, text, dates and errors.
How to count cells greater than a number in Excel
=COUNTIF(B2:B8,">"&E1)Use COUNTIF with the operator in quotes joined to the cell: =COUNTIF(range,">"&E1).
How to count cells that start with specific text in Excel
=COUNTIF(A2:A12,D1&"*")Put the asterisk wildcard after the text:
=COUNTIF(A2:A12,D1&"*")counts every cell that begins with the text in D1.How to count cells that end with specific text in Excel
=COUNTIF(A2:A10,"*"&D1)Put the asterisk before the text:
=COUNTIF(A2:A10,"*"&D1)counts every cell that ends with the text in D1, such as an email domain.How to do a case-sensitive count in Excel
=SUMPRODUCT(--EXACT(B2:B10,E1))COUNTIF ignores case, so use EXACT inside SUMPRODUCT:
=SUMPRODUCT(--EXACT(B2:B10,E1))counts only cells that match E1 letter for letter.How to count cells that contain any text in Excel
=COUNTIF(B2:B10,"*")Use a lone asterisk as the criteria:
=COUNTIF(B2:B10,"*")counts cells that hold text and skips numbers, dates and blanks.How to count cells that contain numbers in Excel
=COUNT(B2:B11)Use
=COUNT(B2:B11). COUNT includes numbers and dates and skips text, blanks and errors.How to count cells not equal to a value in Excel
=COUNTIF(B2:B11,"<>"&E1)Join the "not equal" operator to the value:
=COUNTIF(B2:B11,"<>"&E1)counts every cell that isn't the value in E1, blanks included.How to exclude several values from a count with COUNTIFS in Excel
=COUNTIFS(B2:B11,"<>"&F1,B2:B11,"<>"&F2)Give COUNTIFS one "not equal" condition per value on the same range:
=COUNTIFS(B2:B11,"<>"&F1,B2:B11,"<>"&F2).How to count blank cells in Excel
=COUNTBLANK(C2:C11)Use
=COUNTBLANK(C2:C11). It counts empty cells, and also cells whose formula returns empty text ("").How to count cells with errors in Excel
=SUMPRODUCT(--ISERROR(D2:D9))Test every cell with ISERROR and add up the TRUEs:
=SUMPRODUCT(--ISERROR(D2:D9)).How to count dates by month in Excel
=COUNTIFS($B$2:$B$13,">="&E2,$B$2:$B$13,"<="&EOMONTH(E2,0))Put the first day of each month in a column and use COUNTIFS from that day to the month end:
=COUNTIFS($B$2:$B$13,">="&E2,$B$2:$B$13,"<="&EOMONTH(E2,0)).How to count dates in a given year in Excel
=COUNTIFS(B2:B11,">="&DATE(G1,1,1),B2:B11,"<"&DATE(G1+1,1,1))Count from January 1 up to the next January 1:
=COUNTIFS(B2:B11,">="&DATE(G1,1,1),B2:B11,"<"&DATE(G1+1,1,1)), with the year in G1.How to count dates by quarter in Excel
=COUNTIFS(B2:B13,">="&DATE(G1,G2*3-2,1),B2:B13,"<"&DATE(G1,G2*3+1,1))Turn the year and quarter into a date range:
=COUNTIFS(B2:B13,">="&DATE(G1,G2*3-2,1),B2:B13,"<"&DATE(G1,G2*3+1,1)).How to count dates that fall on a weekend in Excel
=SUMPRODUCT(--(WEEKDAY(B2:B11,2)>5))Use WEEKDAY with return type 2, where Saturday is 6 and Sunday is 7:
=SUMPRODUCT(--(WEEKDAY(B2:B11,2)>5)).How to count dates by day of the week in Excel
=SUMPRODUCT(--(TEXT($B$2:$B$15,"ddd")=E2))Compare each date's short day name with the day in your table:
=SUMPRODUCT(--(TEXT($B$2:$B$15,"ddd")=E2)), filled down from Mon to Sun.How to count values between two numbers in Excel
=COUNTIFS(B2:B13,">="&E1,B2:B13,"<="&E2)Use COUNTIFS with a lower and an upper bound on the same range:
=COUNTIFS(B2:B13,">="&E1,B2:B13,"<="&E2).How to use COUNTIF with OR conditions in Excel
=SUMPRODUCT(COUNTIF(B2:B11,E1:E2))Give COUNTIF a list of criteria and add up the counts:
=SUMPRODUCT(COUNTIF(B2:B11,E1:E2))counts cells equal to E1 or E2.How to count cells that match any value in a list in Excel
=SUMPRODUCT(--(COUNTIF(E2:E4,B2:B13)>0))Ask COUNTIF whether each cell appears in the list:
=SUMPRODUCT(--(COUNTIF(E2:E4,B2:B13)>0))counts the rows whose value is on the list.How to count with OR across two columns in Excel
=SUMPRODUCT(--((B2:B11=F1)+(C2:C11>F2)>0))Add the two tests and count rows where the sum is above zero:
=SUMPRODUCT(--((B2:B11=F1)+(C2:C11>F2)>0)).How to count unique values with criteria in Excel
=COUNTA(UNIQUE(FILTER(A2:A13,B2:B13=G1)))Filter the rows that meet the condition, then count the unique values left:
=COUNTA(UNIQUE(FILTER(A2:A13,B2:B13=G1))).How to count distinct customers per region in Excel
=COUNTA(UNIQUE(FILTER($A$2:$A$13,$B$2:$B$13=E2)))Next to each region, count the unique customers in that region:
=COUNTA(UNIQUE(FILTER($A$2:$A$13,$B$2:$B$13=E2))), filled down.How to count duplicate values in Excel
=SUMPRODUCT(--(COUNTIF(A2:A12,A2:A12)>1))Count the rows whose value appears more than once:
=SUMPRODUCT(--(COUNTIF(A2:A12,A2:A12)>1)).How to count how many times each value appears in Excel
=HSTACK(UNIQUE(A2:A12),COUNTIF(A2:A12,UNIQUE(A2:A12)))In Excel 365, list each value once and count it in one formula:
=HSTACK(UNIQUE(A2:A12),COUNTIF(A2:A12,UNIQUE(A2:A12))).How to count cells equal to a specific value in Excel
=COUNTIF(B2:B12,E1)Use
=COUNTIF(B2:B12,E1), with the value you're counting in E1.How to count new customers per month in Excel
=SUMPRODUCT(($B$2:$B$15>=E2)*($B$2:$B$15<=EOMONTH(E2,0))*(COUNTIFS($A$2:$A$15,$A$2:$A$15,$B$2:$B$15,"<"&$B$2:$B$15)=0))Count orders in the month whose customer has no earlier order:
=SUMPRODUCT(($B$2:$B$15>=E2)*($B$2:$B$15<=EOMONTH(E2,0))*(COUNTIFS($A$2:$A$15,$A$2:$A$15,$B$2:$B$15,"<"&$B$2:$B$15)=0)).How to create a running count of occurrences in Excel
=COUNTIF($B$2:B2,B2)Count from the top of the column down to the current row, with only the start locked:
=COUNTIF($B$2:B2,B2), filled down.How to count how many times a word appears in a range in Excel
=SUMPRODUCT((LEN(A2:A9)-LEN(SUBSTITUTE(LOWER(A2:A9),LOWER(D1),"")))/LEN(D1))Measure how much shorter the text gets when the word is removed:
=SUMPRODUCT((LEN(A2:A9)-LEN(SUBSTITUTE(LOWER(A2:A9),LOWER(D1),"")))/LEN(D1)).How to count cells above or below the average in Excel
=COUNTIF(B2:B11,">"&AVERAGE(B2:B11))Join the operator to AVERAGE of the same range:
=COUNTIF(B2:B11,">"&AVERAGE(B2:B11)). Swap in"<"to count below.How to count cells with a specific number of characters in Excel
=SUMPRODUCT(--(LEN(A2:A11)=E1))Compare LEN of each cell with the length you want:
=SUMPRODUCT(--(LEN(A2:A11)=E1)).How to count a value across multiple columns in Excel
=COUNTIF(B2:F6,H1)Give COUNTIF the whole block of columns as its range:
=COUNTIF(B2:F6,H1)counts every cell in all five columns that equals H1.How to count rows where any column meets a condition in Excel
=SUMPRODUCT(--((B2:B9>=G1)+(C2:C9>=G1)+(D2:D9>=G1)>0))Add a test per column and count rows where the total is above zero:
=SUMPRODUCT(--((B2:B9>=G1)+(C2:C9>=G1)+(D2:D9>=G1)>0)).How to count Yes and No answers in Excel
=COUNTIF($B$2:$B$12,E2)Use one COUNTIF per answer:
=COUNTIF($B$2:$B$12,E2)with "Yes" in E2 and "No" in E3, filled down.How to count checked checkboxes in Excel and Google Sheets
=COUNTIF(B2:B9,TRUE)A checked box holds TRUE, so count the TRUEs:
=COUNTIF(B2:B9,TRUE).How to count by month and category with COUNTIFS in Excel
=COUNTIFS($C$2:$C$13,$E2,$B$2:$B$13,">="&F$1,$B$2:$B$13,"<="&EOMONTH(F$1,0))Build a grid with categories down and month starts across, and use mixed references:
=COUNTIFS($C$2:$C$13,$E2,$B$2:$B$13,">="&F$1,$B$2:$B$13,"<="&EOMONTH(F$1,0)).Why COUNTIF with wildcards doesn't count numbers in Excel
=SUMPRODUCT(--ISNUMBER(SEARCH(E1,A2:A11)))Wildcards only match text, so
"*2026*"skips real numbers. Search the digits instead:=SUMPRODUCT(--ISNUMBER(SEARCH(E1,A2:A11))).Why COUNTIF returns 0 when the value is there
=SUMPRODUCT(--(TRIM(A2:A10)=E1))Hidden spaces are the usual cause. Compare trimmed values:
=SUMPRODUCT(--(TRIM(A2:A10)=E1)).How to count non-zero values in Excel
=COUNTIFS(B2:B11,"<>0",B2:B11,"<>")Exclude zeros and blanks in one COUNTIFS:
=COUNTIFS(B2:B11,"<>0",B2:B11,"<>").How to count negative numbers in Excel
=COUNTIF(B2:B13,"<0")Use
=COUNTIF(B2:B13,"<0"). Zero is not negative, so it is left out.How to count cells that contain one of several words in Excel
=SUMPRODUCT(--((ISNUMBER(SEARCH(E1,A2:A10))+ISNUMBER(SEARCH(E2,A2:A10)))>0))Add a SEARCH test per word and count the cells where at least one is found:
=SUMPRODUCT(--((ISNUMBER(SEARCH(E1,A2:A10))+ISNUMBER(SEARCH(E2,A2:A10)))>0)).How to count cells that contain both of two words in Excel
=COUNTIFS(A2:A10,"*"&E1&"*",A2:A10,"*"&E2&"*")Use COUNTIFS with one wildcard condition per word on the same range:
=COUNTIFS(A2:A10,"*"&E1&"*",A2:A10,"*"&E2&"*").How to count cells that do not contain specific text in Excel
=COUNTIF(A2:A11,"<>*"&E1&"*")Put "not equal" in front of the wildcard pattern:
=COUNTIF(A2:A11,"<>*"&E1&"*").How to count times between two times in Excel
=COUNTIFS(B2:B13,">="&F1,B2:B13,"<"&F2)Times are fractions of a day, so COUNTIFS compares them like numbers:
=COUNTIFS(B2:B13,">="&F1,B2:B13,"<"&F2).How to count overdue tasks in Excel
=COUNTIFS(B2:B11,"<"&G1,C2:C11,"<>Done")Count tasks due before today that aren't done:
=COUNTIFS(B2:B11,"<"&G1,C2:C11,"<>Done"), with=TODAY()or an as-of date in G1.How to count dates in the last 30 days in Excel
=COUNTIFS(B2:B13,">"&G1-G2,B2:B13,"<="&G1)Count dates after the as-of date minus 30 and up to the as-of date:
=COUNTIFS(B2:B13,">"&G1-G2,B2:B13,"<="&G1), with=TODAY()in G1.How to count birthdays by month in Excel
=SUMPRODUCT(--(MONTH(B2:B12)=G1))Compare the month of each date, whatever the year:
=SUMPRODUCT(--(MONTH(B2:B12)=G1)), with the month number in G1.How to count rows where two columns match in Excel
=SUMPRODUCT(--(B2:B11=C2:C11))Compare the columns row by row and add up the matches:
=SUMPRODUCT(--(B2:B11=C2:C11)).How to count values that appear only once in Excel
=SUMPRODUCT(--(COUNTIF(A2:A12,A2:A12)=1))Count the rows whose value has a COUNTIF of exactly 1:
=SUMPRODUCT(--(COUNTIF(A2:A12,A2:A12)=1)).How to count unique combinations of two columns in Excel
=ROWS(UNIQUE(A2:B13))Give UNIQUE both columns and count the rows it returns:
=ROWS(UNIQUE(A2:B13)).How to count rows where one column is greater than another in Excel
=SUMPRODUCT(--(C2:C11>B2:B11))Compare the two columns directly inside SUMPRODUCT:
=SUMPRODUCT(--(C2:C11>B2:B11)).How to count values in ranges like age groups in Excel
=COUNTIFS($B$2:$B$13,">="&E2,$B$2:$B$13,"<="&F2)List each group's lower and upper bound and count between them:
=COUNTIFS($B$2:$B$13,">="&E2,$B$2:$B$13,"<="&F2), filled down.Why COUNTA counts cells that look empty in Excel
=SUMPRODUCT(--(C2:C10<>""))Formulas that return "" aren't empty, so COUNTA counts them. Count cells that show something instead:
=SUMPRODUCT(--(C2:C10<>"")).How to count odd and even numbers in Excel
=SUMPRODUCT(--(MOD(B2:B11,2)=1))Use MOD to get the remainder after dividing by 2:
=SUMPRODUCT(--(MOD(B2:B11,2)=1))counts odd numbers; use=0for even.How to count cells that match a regex in Excel and Google Sheets
=SUMPRODUCT(--REGEXTEST(A2:A11,D1))Excel 365 has REGEXTEST:
=SUMPRODUCT(--REGEXTEST(A2:A11,D1)). In Google Sheets, use REGEXMATCH in the same place.How to count rows where every column is filled in Excel
=COUNTIFS(B2:B11,"<>",C2:C11,"<>",D2:D11,"<>")Give COUNTIFS a "not blank" condition for each column:
=COUNTIFS(B2:B11,"<>",C2:C11,"<>",D2:D11,"<>").How to count entries on a date when the cells include times in Excel
=COUNTIFS(B2:B11,">="&G1,B2:B11,"<"&G1+1)Count from midnight on the date up to midnight the next day:
=COUNTIFS(B2:B11,">="&G1,B2:B11,"<"&G1+1).How to count a specific character in a cell in Excel
=LEN(A2)-LEN(SUBSTITUTE(A2,$E$1,""))Subtract the length without the character from the full length:
=LEN(A2)-LEN(SUBSTITUTE(A2,$E$1,"")).How to count comma-separated items in a cell in Excel
=IF(TRIM(B2)="",0,LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1)Count the commas and add 1, returning 0 for an empty cell:
=IF(TRIM(B2)="",0,LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1).How to count attendance in Excel
=COUNTIF(B2:F2,"P")Count each student's "P" marks across the day columns:
=COUNTIF(B2:F2,"P"), filled down.How to count duplicate rows based on two columns in Excel
=SUMPRODUCT(--(COUNTIFS(A2:A11,A2:A11,B2:B11,B2:B11)>1))Use COUNTIFS on both columns to see how often each pair occurs, and count the pairs that occur more than once:
=SUMPRODUCT(--(COUNTIFS(A2:A11,A2:A11,B2:B11,B2:B11)>1)).How to count rows where one column is filled and another is blank in Excel
=COUNTIFS(B2:B11,"<>",C2:C11,"")Combine "not blank" and "blank" in COUNTIFS:
=COUNTIFS(B2:B11,"<>",C2:C11,"").How to count dates after a certain date in Excel
=COUNTIF(B2:B11,">"&E1)Join the "greater than" operator to the date cell:
=COUNTIF(B2:B11,">"&E1).How to make COUNTIFS ignore a criterion when its cell is blank
=COUNTIFS(B2:B13,IF(G1="","*",G1),C2:C13,IF(G2="","*",G2))Replace a blank criterion with the "*" wildcard:
=COUNTIFS(B2:B13,IF(G1="","*",G1),C2:C13,IF(G2="","*",G2)).How to count how many times a value changes in a column in Excel
=SUMPRODUCT(--(B3:B13<>B2:B12))Compare each cell with the one above it, using two ranges offset by a row:
=SUMPRODUCT(--(B3:B13<>B2:B12)).Why COUNTIFS with dates returns the wrong count in Excel
=SUMPRODUCT((--B2:B11>=G1)*(--B2:B11<=G2))Usually some "dates" are text, which COUNTIFS skips. Convert them inside the formula:
=SUMPRODUCT((--B2:B11>=G1)*(--B2:B11<=G2)), or fix the column.How to count unique values across multiple columns in Excel
=COUNTA(UNIQUE(TOCOL(B2:D6,1)))Stack the columns into one with TOCOL, skipping blanks, then count the unique values:
=COUNTA(UNIQUE(TOCOL(B2:D6,1))).How to count correct answers against an answer key in Excel
=SUMPRODUCT(--(B3:F3=B$2:F$2))Compare each student's row with the locked key row:
=SUMPRODUCT(--(B3:F3=B$2:F$2)), filled down.How to count records active on a given date in Excel
=COUNTIFS(B2:B11,"<="&G1,C2:C11,">="&G1)+COUNTIFS(B2:B11,"<="&G1,C2:C11,"")Count records that started on or before the date and end on or after it, plus those with no end date:
=COUNTIFS(B2:B11,"<="&G1,C2:C11,">="&G1)+COUNTIFS(B2:B11,"<="&G1,C2:C11,"").How to count cells that contain a whole word in Excel
=SUMPRODUCT(--ISNUMBER(SEARCH(" "&E1&" "," "&A2:A10&" ")))Pad the text and the word with spaces so only whole words match:
=SUMPRODUCT(--ISNUMBER(SEARCH(" "&E1&" "," "&A2:A10&" "))).How to count cells with extra spaces in Excel
=SUMPRODUCT(--(LEN(A2:A10)<>LEN(TRIM(A2:A10))))Compare each cell's length with its trimmed length:
=SUMPRODUCT(--(LEN(A2:A10)<>LEN(TRIM(A2:A10)))).How to count numbers stored as text in Excel
=SUMPRODUCT(ISTEXT(B2:B11)*ISNUMBER(--B2:B11))Count cells that are text but turn into a number when converted:
=SUMPRODUCT(ISTEXT(B2:B11)*ISNUMBER(--B2:B11)).How to count dates by week in Excel
=COUNTIFS($B$2:$B$13,">="&E2,$B$2:$B$13,"<"&E2+7)List each week's start date and count the seven days from it:
=COUNTIFS($B$2:$B$13,">="&E2,$B$2:$B$13,"<"&E2+7), filled down.How to count ties for the highest value in Excel
=COUNTIF(B2:B11,MAX(B2:B11))Count how many cells equal the maximum:
=COUNTIF(B2:B11,MAX(B2:B11)).How to count dates in a fiscal year in Excel
=COUNTIFS(B2:B12,">="&DATE(G1-1,G2,1),B2:B12,"<"&DATE(G1,G2,1))Count from the fiscal year's first day up to the next one's:
=COUNTIFS(B2:B12,">="&DATE(G1-1,G2,1),B2:B12,"<"&DATE(G1,G2,1)), with the fiscal year in G1 and its start month in G2.How to count rows that meet at least two of three conditions in Excel
=SUMPRODUCT(--((B2:B11>=G1)+(C2:C11>=G2)+(D2:D11=G3)>=2))Add the three tests and count rows where the total is 2 or more:
=SUMPRODUCT(--((B2:B11>=G1)+(C2:C11>=G2)+(D2:D11=G3)>=2)).How to count values within a tolerance of a target in Excel
=COUNTIFS(B2:B11,">="&E1-E2,B2:B11,"<="&E1+E2)Count between the target minus and plus the tolerance:
=COUNTIFS(B2:B11,">="&E1-E2,B2:B11,"<="&E1+E2).