Count Specific Texts and Numbers that Meet Single Condition with COUNTIF

COUNTIF allows you to summarize tables easily. Imagine you have a big poll, maybe thousands of registers and you want to know: how many NO and YES responses you have. In brief, you want to turn the big table into a compact table:

Graphic 1

Countif table

Countif table

No Pivot Tables this time.

Well, this has been an easy start; let’s explore this function in more detail…

Visualize a large list about a survey (see graphic 2).

Let’s start counting YES and NO responses. B20 formula counts each cell in D3:D12 that matches “YES” (A20).

Copy and paste the formula to B21 and done. Almost done, never forget to cross check; in this example the total must be always 10.

Graphic 2

Countif table

Countif table


You usually copy and paste this formula downward so apply this tip:

Tip 1: Use always Absolute Reference for the “range” argument in COUNTIF function.

Now it is time to Count the number of respondents that match each Spending Category, see graphic 3. Formula in B27 counts each cell in C3:C12 that matches all values less/equal than 500.

The formula in B28 is as follows:

=COUNTIF($C$3:$C$12;”<=1000″)-COUNTIF($C$3:$C$12;”<=500″)

The formula in B29 is as follows:

=COUNTIF($C$3:$C$12;”>1000″)

Graphic 3

Countif formula

Countif formula