Ask Sawal

Discussion Forum
Notification Icon1
Write Answer Icon
Add Question Icon

Count x's in excel?

3 Answer(s) Available
Answer # 1 #

In many cases, you may need to count cells that contain text value “juice” or text value “pie” in Excel as the left screenshot shown. This article will show you some methods to achieve it.

Count cells match to either X or Y in Excel with formula Count cells match to either X or Y in Excel with User-defined function Easily count cells match to either X or Y in Excel with Kutools for Excel

The following formula will help you to count cells match to either value X or Y in Excel. Please do as follows.

1. Select a blank cell, enter the below formula into it and then press the Enter key.

=COUNTIF(A2:A7,"*juice*")+COUNTIF(A2:A7,"*pie*")

Note: In the formula, you can see there are wildcard characters * display on the left and right sides of the certain text value. You can remove the wildcard characters if you want to count cells exactly equal to either X or Y.

Then you will get the total number of cells match to either text value “juice” or “pie” in Excel.

You can also create a User-defined function to count cells match to either X or Y in Excel.

1. Press Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy and paste the following VBA code into the Module window.

VBA code: Count cells match to either X or Y in Excel

3. Press Alt + Q keys to close the Microsoft Visual Basic for Applications window.

4. Now select a blank cell, enter the below formula into it and then press the Enter key to get the result.

=FindTwoStrings(A2:A7,"juice","pie")

Here we recommend you the Select Specific Cells utility of Kutools for Excel. With this utility, you can easily count cells match to either X or Y in a specified range in Excel. Please do as follows.

1. Select the range you need to count, then click Kutools > Select > Select Specific Cells. See screenshot:

2. In the Select Specific Cells dialog box, you need to:

3. Then another Select Specific Cells dialog box pops up to tell you how many cells were found and selected. After that, you can click the OK button to close it.

[3]
Edit
Query
Report
RJ Lawther
Novelist
Answer # 2 #
  • Select a cell.
  • Type =COUNTIF.
  • Double click the COUNTIF command.
  • Select a range.
  • Type ,
  • Select a cell (the criteria, the value that you want to count)
  • Hit enter.
[3]
Edit
Query
Report
Answer # 3 #

The COUNTIF function is a premade function in Excel, which counts cells as specified.

It is typed =COUNTIF

Numbers (e.g. 90) and words (e.g. "Water") can be specified.

How to use the =COUNTIF function:

Let's see some examples!

Apply the COUNTIF function to range B2:B21, to count how many Pokemons we have in the different types:

We want the COUNTIF function to count the types of Pokemons, in the range G5:G15:

The , is typed after the range is selected, which tells the function what you are looking to count.

The COUNTIF function has successfully counted 1 Grass Pokemon, which is Victreebel (A4).

Let's count the Water Pokemons in G6

The same steps apply

That is great! The COUNTIF function has counted 6 Water Pokemons in B2:B21.

Let's count the rest of the types more effectively. We want to continue the function from G6:G15. Making use of the Filling Function and Absolute References.

Step by step:

That saved us alot of time! Good job!

Let's try an example that will not work

Fill G5:G15 without locking the references to see what happens.

If the references for the range are kept relative, the fill function will move the range downwards, including blank cells and missing the range where the data is.

[0]
Edit
Query
Report
Temístocles Appleby
Exotic Dancer