How to count cells by criteria

Excel
Use COUNTIF if you need to sum values for a particular person or other criterion.

To count cells by criteria, do the following:

1.   Select the cell where want Excel to return the number of cell by criteria. 2.   Do one of the following:

• On the Formula tab, in the Function Library group, select the More Functions button and then select Statistical: Choose COUNTIF in the list.

• Click the Insert Function button in the left of the edit bar: In the Insert Function dialog box: • select Statistical in the Or select a category listbox
• select COUNTIF in the Select a function list

3.   In the Function Arguments dialog box, full following fields: • The Range field determines the range of cells Excel will look to perform the count in. In this example the cell range is D2:D21.
• The Criteria is a conditional statement that is similar to the conditional statement in the IF statement.

4.   Press OK. Note: You can enter this formula using keyboard, for this example:

= COUNTIF (D2:D21, ">200")

If you want to use COUNTIF on a selection of cells (not necessarily one solid range), summing multiple COUNTIFs:

= SUM (COUNTIF (D3, ">100"), COUNTIF (D7, ">100"), COUNTIF (D14, ">100"), COUNTIF (D17, ">100"))

Note:

• You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
• Microsoft Excel provides additional functions that can be used to analyze your data based on a condition.
• To calculate a sum based on a string of text or a number within a range, use the SUMIF worksheet function (see How to sum cells by criteria for more details).
• To have a formula return one of two values based on a condition, such as a sales bonus based on a specified sales amount, use the IF worksheet function.
• To count cells that are empty or not empty, use the COUNTA and COUNTBLANK functions.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.
{"ft_lang_label":"__label__en","ft_lang_prob":0.8161674,"math_prob":0.82544416,"size":2036,"snap":"2019-26-2019-30","text_gpt3_token_len":468,"char_repetition_ratio":0.13484251,"word_repetition_ratio":0.0,"special_character_ratio":0.24607073,"punctuation_ratio":0.13216957,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.97021693,"pos_list":[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14],"im_url_duplicate_count":[null,1,null,1,null,1,null,1,null,1,null,1,null,1,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2019-06-17T03:02:24Z\",\"WARC-Record-ID\":\"<urn:uuid:a5bf9f17-7d65-45d4-986e-6cd2b40d47e3>\",\"Content-Length\":\"22556\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:15bce4a3-4391-4915-942e-b71db802f982>\",\"WARC-Concurrent-To\":\"<urn:uuid:7469fc65-85fa-42a5-8717-22670e8013b6>\",\"WARC-IP-Address\":\"104.31.84.188\",\"WARC-Target-URI\":\"https://www.officetooltips.com/excel_2010/tips/how_to_count_cells_by_criteria.html\",\"WARC-Payload-Digest\":\"sha1:C4UW2QKZTN2UMHD5MZP346N3CEKEYLWE\",\"WARC-Block-Digest\":\"sha1:S4BW4KUYMCDWMUGPXQKZTBXA5QLUV7OK\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2019/CC-MAIN-2019-26/CC-MAIN-2019-26_segments_1560627998369.29_warc_CC-MAIN-20190617022938-20190617044938-00287.warc.gz\"}"}