How to count data in Excel

Cell Counting Techniques Category: Formulas | [Item URL] Excel provides many ways to count cells in a range that meet various criteria: The DCOUNT function. The data must be set up in a table, and a separate criterion range is required. The COUNT function. Simply counts the number of cells in a range that contain a number. The COUNTA function. Counts the number of non-empty cells in a range. The COUNTBLANK function. Counts the number of empty cells in a range. The COUNTIF function. Very flexible, but often not quite flexible enough. An array formula. Useful when the other techniques won't work. Formula Examples Listed below are some formula examples that demonstrate various counting techniques. These formula all use a range named data. To count the number of cells that contain a negative number: =COUNTIF(data,"<0 10:="COUNTIF(data," 1="" a="" and="" any="" begins="" between="" case-sensitive="" case="" cells="" contain="" count="" data="" either="" letter="" no="" not="" number="" of="" or="" s="" sensitive="" text:="COUNTIF(data," text="" that="" the="" three-letter="" to="" value="" with="" word="" words:="COUNTIF(data," yes="">=1")-COUNTIF(data,">10") To count the number of unique numeric values (ignores text entries): =SUM(IF(FREQUENCY(data,data)>0,1,0)) To count the number of cells that contain an error value (this is an array formula, entered with Ctrl+Shift+Enter): =SUM(IF(ISERR(data),1,0)) Using the formulas in VBA You can also use these techniques in your VBA code. For example the VBA statement below calculates the number of three-letter words in a range named data, and assigns the value to the NumWords variable: NumWords = Application.COUNTIF(Sheets("Sheet1").Range("data"), "???") The other formula examples listed above can also be converted to VBA.
<0 10:="COUNTIF(data," 1="" a="" and="" any="" begins="" between="" case-sensitive="" case="" cells="" contain="" count="" data="" either="" letter="" no="" not="" number="" of="" or="" s="" sensitive="" text:="COUNTIF(data," text="" that="" the="" three-letter="" to="" value="" with="" word="" words:="COUNTIF(data," yes="">
<0 10:="COUNTIF(data," 1="" a="" and="" any="" begins="" between="" case-sensitive="" case="" cells="" contain="" count="" data="" either="" letter="" no="" not="" number="" of="" or="" s="" sensitive="" text:="COUNTIF(data," text="" that="" the="" three-letter="" to="" value="" with="" word="" words:="COUNTIF(data," yes=""> Ref: http://spreadsheetpage.com