Google Sheets COUNTIF is one of the easiest functions to learn and one of the handiest to use.

It's time to pick up some knowledge on how COUNTIF is used in Google Spreadsheet and learn why this function makes a true Google Spreadsheet companion.

## What is the COUNTIF function in Google Sheets?

This short helper allows us to count how many times a certain value appears within a specified data range.

## COUNTIF syntax in Google Sheets

The syntax of our function and its arguments are as follows:

=COUNTIF(range, criterion)
• range - a range of cells where we want to count a certain value. Required.
• criterion or searching criterion - a value to find and count across the data range indicated in the first argument. Required.

It may seem that COUNTIF is so simple that it doesn't even count as a function (pun intended), but in truth its potential is quite impressive. Its searching criteria alone is enough to earn such a description.

The thing is that we can decide to look for not only concrete values but also those that meet certain criteria.

It's high time to try and build a formula together.

Let's suppose your company sells various types of chocolate in several consumer regions and works with many clients.

Let's begin with the basics.

We need to count the number of "Milk Chocolate" sold. Place the cursor in the cell where you want to get the result and enter the equality sign (=). Google Sheets immediately understands that we are going to enter a formula. As soon as you type the letter "C", it will prompt you to choose a function that begins with this letter. Select "COUNTIF".

The first argument of COUNTIF is represented by the following range: D6:D16. By the way, you don't have to enter the range manually - mouse selection is enough. Then enter a comma (,) and specify the second argument - searching criteria.

The second argument is a value that we're going to look for across the selected range. In our case it's going to be the text - "Milk Chocolate". Remember to finish the function with a closing bracket ")" and press "Enter".

Also, don't forget to enter double quotes ("") when using text values.

Our final formula looks as follows:

`=COUNTIF(D6:D16,"Milk Chocolate")`

As a result, we get three sales of this type of chocolate.

Note. COUNTIF function works with a single cell or neighboring columns. In other words, you can't indicate a few separate cells or columns and rows. Please see the examples below.

Incorrect formulas:

`=COUNTIF(C6:C16, D6:D16,"Milk Chocolate")`

`=COUNTIF(D6, D8, D10, D12, D14,"Milk Chocolate")`

Correct usage:

`=COUNTIF(C6:D16,"Milk Chocolate")`

`=COUNTIF(D6,"Milk Chocolate") + COUNTIF(D8,"Milk Chocolate") + COUNTIF(D10,"Milk Chocolate") + COUNTIF(D12,"Milk Chocolate") + COUNTIF(D14,"Milk Chocolate")`

You may have noticed that it's not really convenient to set the searching criteria in the formula - you have to edit it every time. The better decision would be to write the criteria down other Google Sheets cell and reference that cell in the formula.

Let's count the number of occurred sales in the "West" region using the cell reference in COUNTIF. We'll get the following formula:

`=COUNTIF(C6:C16,A3)`

The function uses the content of A3 (the text value "West") in its calculations. As you can see, it's a lot easier now to edit the formula and its searching criteria.

Of course, we can do the same thing with numerical values. We can count the number of occurrences of the number "125" by indicating the number itself as a second argument:

`=COUNTIF(E7:E17,125)`

or by replacing it with a cell reference:

`=COUNTIF(E7:E17,A3)`

What is great about COUNTIF is that it can count whole cells as well as parts of the cell's contents. For that purpose, we use wildcard characters: "?", "*".

For instance, to count the sales in some particular region we can use only the part of its name: enter "?est" into B3. A question mark (?) replaces one character. We are going to look for the 4-letter words ending with "est", including spaces.

Use the following COUNTIF formula in B3:

`=COUNTIF(C7:C17,A3)`

As you already know, the formula can easily take the next form:

`=COUNTIF(C7:C17, "?est")`

And we can see 5 sales in the "West" region.

Now let us employ the B4 cell for another formula:

`=COUNTIF(C7:C17,A4)`

What is more, we'll change the criteria to "??st" in A4. It means that now we are going to look for 4-letter words ending with "st". Since in this case two regions ("West" and "East") satisfy our criteria, we will see nine sales:

Similarly, we can count the number of sales of the goods using an asterisk (*). This symbol replaces not just one, but any number of characters:

"*Chocolate" criteria counts all the products ending with "Chocolate".

"Chocolate*" criteria counts all the products starting with "Chocolate".

And, as you may guess, if we enter "*Chocolate*", we're going to look for all the products that contain the word "Chocolate".

Note. If you need to count the number of words that contain an asterisk (*) and a question mark (?), then use tilde sign (~) before those characters. In this case, COUNTIF will treat them as simple signs rather than searching characters. For example, if we want to look for the values that contain "?", the formula will be:

`=COUNTIF(D7:D15,"*~?*")`

### COUNTIF Google Sheets for less than, greater than or equal to

The COUNTIF function is able to count not only how many times some number appears, but also how many of the numbers are greater than/less than/equal to/not equal to another specified number.

For that purpose, we use corresponding mathematical operators: "=", ">", "<", ">=", "<=", "<>".

Check out the table below to see how it works:

 Criteria Formula example Description The number is greater than `=COUNTIF(F9:F19,">100")` Count cells where values are greater than 100. The number is less than `=COUNTIF(F9:F19,"<100")` Count cells where values are less than 100. The number equals to `=COUNTIF(F9:F19,"=100")` Count cells where values equal to 100. The number is not equal to `=COUNTIF(F9:F19,"<>100")` Count cells where values are not equal to 100. The number is greater than or equal to `=COUNTIF(F9:F19,">=100")` Count cells where values are greater than or equal to 100. The number is less than or equal to `=COUNTIF(F9:F19,"<=100")` Count cells where values are less than or equal to 100.
Note. It's very important to enclose the mathematical operator along with a number in the double quotes.

If you want to change the criteria without altering the formula, you can reference the cells as well.

Let us reference A3 and put the formula in B3, just as we did before:

`=COUNTIF(F9:F19,A3)`

To create more sophisticated criteria, use an ampersand (&).

For example, B4 contains a formula which counts the number of values greater than or equal to 100 in the E9:E19 range:

`=COUNTIF(E9:E19,">="&A4)`

B5 has the very same criteria, but we reference not only the number in that cell but also a mathematical operator. This makes it even easier to adapt COUNTIF formula if necessary:

`=COUNTIF(E9:E19,A6&A5)`

Tip. We've been asked a lot about counting those cells that are greater than or less than values in another column. If that's what you're looking for, you will need another function for the job — SUMPRODUCT.

For example, let's count all rows where sales in column F are bigger than in the same row of column G:

`=SUMPRODUCT(--(F6:F16>G6:G16))`

• The part at the core of the formula — F6:F16>G6:G16 — compares values in columns F and G. When the number in column F is greater, the formula takes it as TRUE, otherwise — FALSE.

You'll see that if you enter the same into the ArrayFormula:

`=ArrayFormula(F6:F16>G6:G16)`

• Then the formula takes this TRUE/FALSE result and turns it into 1/0 numbers with the help of the double unary operator (--).
• This lets SUM do the rest — total the number of when F is greater than G.

Sometimes it's necessary to count the number of values that answer at least one of the mentioned conditions (OR logic) or multiple criteria at once (AND logic). Based on that, you can use either a few COUNTIF functions in a single cell at a time or the alternate COUNTIFS function.

#### Count in Google Sheets with multiple criteria — AND logic

The only way I’d advise you to use here is with a special function that is designed to count by multiple criteria — COUNTIFS:

=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])

It is normally used when there are values in two ranges that should meet some criteria or whenever you need to get the number falling between a specific range of numbers.

Let’s try and count the number of total sales between 200 and 400:

`=COUNTIFS(F8:F18,">=200",F8:F18,"<=400")`

#### Count uniques in Google Sheets with multiple criteria

You can go further and count the number of unique products between 200 and 400.

Nope, it's not the same as above! :) The above COUNTIFS counts each occurrence of sales between 200 and 400. What I suggest is to also look at the product. If its name occurs more than once, it won't be included in the result.

There's a special function for that — COUNTUNIQUEIFS:

COUNTUNIQUEIFS(count_unique_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

Compared to COUNTIFS, it's the first argument that makes the difference. Count_unique_range is that range where the function will count unique records.

Here's how the formula and its result will look:

`=COUNTUNIQUEIFS(D6:D16,F6:F16,">=200",F6:F16,"<=400")`

Look, there are 3 rows that meet my criteria: the sales are 200 and greater and at the same time are 400 or less.

However, 2 of them belong to the same product — Milk Chocolate. COUNTUNIQUEIFS counts the first mention of the product only.

Thus, I know that there are only 2 products that meet my criteria.

#### Count in Google Sheets with multiple criteria — OR logic

When only one of all criteria is enough, you’d better use several COUNTIF functions.

##### Example 1. COUNTIF + COUNTIF

Let's count the number of sales of black and white chocolate. To do that, enter the following formula in B4:

`=COUNTIF(D7:D17,"*Milk*") + COUNTIF(D7:D17,"*Dark*")`

Tip. I use asterisk (*) to ensure that the words "dark" and "milk" will be counted no matter where they are in the cell — at the beginning, in the middle, or at the end.
Tip. You can always introduce cell references to your formulas. See how it looks on the screenshot below in B3, the result remains the same:
##### Example 2. COUNTIF — COUNTIF

Now, I am going to count the number of total sales between 200 and 400:

I take the number of totals under 400 and subtract the number of total sales under 200 using the next formula:

`=C0UNTIF(F7:F17,"<=400") - COUNTIF(F7:F17,"<=200")`

The formula returns the number of sales more than 200 but less than 400.

If you decide to reference A3 and A4 that contain the criteria, the formula will be a bit simpler:

`=COUNTIF(F7:F17, A4) - COUNTIF(F7:F17, A3)`

A3 cell will have "<=200" criteria, while A4 - "<=400". Put both formulas into B3 and B4 and make sure that the result doesn't change — 3 sales over the needed range.

### COUNTIF Google Sheets for blank and non-blank cells

With the help of COUNTIF, we can also count the number of blank or non-blank cells within some range.

Let's suppose that we successfully sold the product and marked it as "Paid". If the customer declined the goods, we write zero (0) in the cell. If the deal wasn't closed, the cell remains empty.

To count non-blank cells with any value, use the following:

`=COUNTIF(F7:F15,"<>")`

or

`=COUNTIF(F7:F15,A3)`

To count the number of empty cells, make sure to put the COUNTIF formula in the following way:

`=COUNTIF(F7:F15,"")`

or

`=COUNTIF(F7:F15,A4)`

The number of cells with a textual value is counted like this:

`=COUNTIF(F7:F15,"*")`

or

`=COUNTIF(F7:F15,A5)`

Screenshot below shows that A3, A4, and A5 cells include our criteria:

Thus, we can see 4 closed deals, 3 of which were paid for and 5 of which have no markings yet and, consequently, are not closed.

### COUNTIF and conditional formatting

There is one interesting opportunity that Google Sheets offer - to change the cell's format (like its color) depending on some criteria. For example, we can highlight the values that appear more often in green.

COUNTIF function can play a small part here as well.

Select the range of the cells that you want to format in some special way. Click Format -> Conditional formatting...

In the Format cells if... drop-down list choose the last option Custom formula is, and enter the following formula into the appeared field:

`=COUNTIF(\$B\$10:\$B\$39,B10)/COUNTIF(\$B\$10:\$B\$39,"*")>0.4`

It means that the condition will be answered if the value from B10 appears within B10:B39 in more than 40% of cases:

In a similar way, we add two more formatting rule criteria - if the cell value appears more often than in 25% of cases and more often than in 15%:

`=COUNTIF(\$B\$10:\$B\$39,B10)/COUNTIF(\$B\$10:\$B\$39,"*")>0.25`

`=COUNTIF(\$B\$10:\$B\$39,B10)/COUNTIF(\$B\$10:\$B\$39,"*")>0.15`

Keep in mind that the first criterion will be checked beforehand, and if it's met, the rest won't apply. That is why you'd better start with the most unique values moving to the most common ones. If the cell value doesn't meet any criteria, its format will remain intact.

You can see that the colour of the cells has changed according to our criteria.

To make sure, we also counted the frequency of some values in C3:C6 using COUNTIF function. The results confirm that COUNTIF in formatting rule was applied correctly.

All these function examples give us a clear understanding of how Google Spreadsheet COUNTIF offers multiple opportunities to work with the data in a most efficient way.

## You may also be interested in

1. Helen says:

Hi!

Thank you very much for this resource and the amazing job you are doing!

I need your help. I would like to count a number of words in a cell (or a range of cells) but I need to exclude the counting of certain words that are irrelevant (say "a", "the", "and", "an"). The range is the same (say B2:B16), but the criteria of exclusion are multiple. How can I do it without repeating the range every time (in fact, there are a lot more the criteria of exclusion than the four in the example above).

• Hi Helen,

Thank you for your lovely words!

I'll check the data you have and see where your criteria are and how many of them you may have. This will help me find the best solution.

• Helen says:

Dear Natalia,

Helen

• Dear Helen,

Thank you for sharing your spreadsheet. Here's a formula to count all those special words you mentioned:
=COUNT(ArrayFormula(FIND("#", REGEXREPLACE(SPLIT(B3, {" ", ",", ";"}), "^(and|the|a|at|in)\$", "#"))))

Subtract this from your total word count and you're good to go :)

I entered the resulting formulas to column E in your spreadsheet, have a look.

2. Cathy says:

In the example below, how would I count the number of shirts of each style, size and color?

Style Size Color
Women's XL Black
Women's XL Black
Men's XL White
Men's 2XL White
Women's XL Green
Men's L Green
Men's 3XL Black
Mens XL Green
Men's XL Black
Women's M Yellow
Women's M Black
Women's XL White
Women's XL Green
Men's XL Black
Women's L Green
Women's XL Green
Men's 3XL Green

• Hello Cathy,

if you need to count styles/sizes/colors separately, use COUNTIF as described here.

If you'd like to count based on several conditions at a time, for example, all women's XL black, use COUNTIFS instead.

• Kirk says:

So trying to add the agent from Column A (9 agents total) to this formula: =ARRAYFORMULA(sum(COUNTIFS(AS2:AS11,{"Yes","VM","No"})))

• Hello Kirk,

3. Ben Brimson says:

Hi,

I have calculated how many completed tasks are done in the current day or current month. See below:

=COUNTIFS('Completed Cases'!K2:K5000,">"&TODAY()-1,'Completed Cases'!K2:K5000,"="&EOMONTH(TODAY(),-1)+1,'Completed Cases'!K2:K5000,"<"&EOMONTH(TODAY(),0)+1)

I now need to work out how to break that down and count if the outcome was option A or option B from a different column. What is the best way to do this please?

• Hi Ben,

Am I getting it right that some results from K belong to option A from another column and some results belong to B? If so and you'd like to get two different results based on that another column as well, you just neet to have two COUNTIFS formulas where one of the conditions will be for that another column:
=COUNTIFS('Completed Cases'K2:K5000, ">"&TODAY()-1, 'Completed Cases'!K2:K5000, "="&EOMONTH(TODAY(),-1)+1, 'Completed Cases'!K2:K5000, "<"&EOMONTH(TODAY(),0)+1, 'Completed Cases'!ANOTHER_COLUMN, "option A")

If that's not really what you need, please try to describe the desired outcome in more detail.

• Hi Jack,

you should use COUNTIFS for your task. I've put an example formula in E2 since it's the only row from your task I understand clearly. Please adjust this formula to use in other rows as you need.

• Jack says:

Hi, I had managed it just before your replies and had sent my solution but it appears it did not go through?

Thanks anyway

4. Jack says:

Hi, I have a sheet with actions on.

On another sheet, I want the number of actions where, on the actions sheet:
- value in column C is the value in column C of the count sheet (e.g. where C9 = C9)
- value in column H of the action sheet is "Strike"
- value in column J of the action sheet is "Active"

How would I achieve this?
Thanks

• Hi Jack,

• Jackk says:

Did it now, thanks anyway.

=COUNTIFS('Log Sheet'!C9:C,C9,'Log Sheet'!H9:H,"Strike",'Log Sheet'!J9:J,"Active")

5. Elizabeth Doerfler says:

Hi,
I'm trying to do something similar. Let's pretend on your sample sheet that you had in Column E another list of products. Some said Dark Chocolate, Milk Chocolate, Extra Dark Chocolate, but you also had just plain Chocolate. etc.
I want to be able to count all of the occurrences of Dark Chocolate where (plain) Chocolate would not be counted.

(In my sheet, I need to count all of the occurrences of English 1 separately from all of the occurrences of Honors English 1.
I can get an exact count of all of the "Honors English 1", but anytime I try to count "English 1", because those words also appear in "Honors English 1", it counts both "English 1" and "Honors English 1".

• Hi Elizabeth,

Thank you for sharing the sample right away. I entered the formulas into column J, please take a look. Here's a sample formula:
=COUNTIF(\$B\$2:\$E\$11,G2)

6. Firdauz Abdullah says:

Hi, is there any possiblitiy for me to use COUNTIFS to count specific text from cell that has Data Validation : drop down list.

• Hi Firdauz,

Do you want to take cells with data validation into account or do you want to count text in all items within one data validation in a cell?

7. shen says:

How to make a countif to find out amount more than 0:03:00 but lower than 0:04:00

• Hello Shen,

You need to use COUNTIFS and list both conditions in the formula. Please see the examples described in this section.

8. Matt says:

Hi! I created a self-assessment in Google Forms that I'm trying to score with a simple formula. All questions on the assessment have checkbox options for each item, so multiple options can be selected. For example...

For question 1, I choose Options A, B, and D. Option A is worth 1 point, Option B is worth 2 points, and option D is worth 4 points. What formula will take a cell that says 'A, B, D' and calculate 7?

• Matt says:

I figured it out this morning. I was overthinking it.

I used =(COUNTIF(cell,"*A*")*1)+(COUNTIF(cell,"*B*")*2)+(COUNTIF(cell,"*C*")*3)+(COUNTIF(cell,"*D*")*4)...

9. Olivia says:

How do i do countif with the power formula?

• Hello Olivia,

10. Ela says:

Hi there,
I am struggling with conditional formatting using custom formula. I want to do a simple formatting into red for all cells in one column that are duplicates (excluding the first occurrence). Following some instructions on the Internet, I select the column (it's formatted as plain text and it only contains numbers) - the range would be A3:A500 (A1 nad A2 are headers) and then Format cells if -> custom formula -> =countif(\$A\$3:\$A\$500,A3)>1. I also try without \$ sign. I also try with (A:A, A3)>1. The problem is that after I have written =countif, no matter what I put there - be it the bracket or whatever - the system highlight this in red and always shows "Invalid formula". Literally, into the custom formula field I can put only: =countif .
I am puzzled and desperate. I have tried on different data in different sheets - same issue everywhere... I would appreciate any help on this matter.

• Hi Ela,

First please try to format your data as numbers, not text, before trying to apply the conditional formatting.

If this doesn't help, for me to be able to help you, please share an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a shortened copy of your source data (2) the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

11. Shawna says:

I have multiple conditional formatting in a google spreadsheet. An example is: =AND(\$B2="In",COUNTIFS(\$D:\$D,\$D2,\$B:\$B,"In")=6)
This will look at multiple cells and highlight a row based upon the criteria. Each conditional formatting is the same just the number at the end changes which, in turn, highlights the cell a different color. I need to add in another factor to this formatting that if something is column E is indicated as either MEDICAL APPOINTMENT or ILLNESS that is does not count as one of the quantities.
For example is someone has 5 that are marked "In" generally it would highlight all that person's lines purple; however, if 1 of those "INs" are due to a medical appointment or illness it needs to not be counted as 5 INs". but 4 "INs" and highlighted a different color

• Hello Shawna,

For me to be able to help you, please share an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a shortened copy of your source data (2) the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

12. DB says:

Hello.

I have a series of cells each containing a 0 or a 1. I would like to count the number of cells containing a 1, but would the count to stop when the first 0 is encountered.

For example, this row:
1 1 1 1 0 1 1 0 0 1 0 0 0
should produce the count 4.

And this one:
1 1 0 1 1 1 1 1 0 1 1 0
should produce the count 2.

Can this be done with COUNTIF (or perhaps with some other function)?

Best wishes

• Hello DB,

Assuming your numbers are in A1:M1, here's a formula to try:
=IFERROR(LEN(LEFT(JOIN("",A1:M1),FIND("0",JOIN("",A1:M1))-1)),COUNT(A1:M1))

13. Robin says:

Hello!

I am trying to count how many values in a column are bigger than the corresponding values in another. Specifically, I have one column that lists the scores of a pre-test that students take at the beginning of class and a list of the student's scores on the test they take at the end of the class. I want to count how many of those scores improved, how would I do that?

Thank you!

• Hello Robin,

Assuming your scores are in columns A and B, this formula will do:
=SUMPRODUCT(--(B2:B20>A2:A20))

14. Abraham says:

Hi! Can you help in writing a countif function where the cell does not contain "---"

• Hi Abraham,

If I understand you correctly, this formula will do the trick:
=COUNTIF(A2:A10,"<>*---*"

15. Jon says:

I wonder whether the following is possible.

In TAB1, column A has a number of people's names. Column B onwards is then made up of dates and each column records attendance the attendance of the names in column A

In TAB2, column A has the same names. I want Column B to then repeat whatever is in the cell of today's column in TAB1, so if H1 has today's date, then it will give me the attendance of the named person (in A9) in B9 in TAB2.

How do I get column B in Tab2 to pick up the relevant cell from today's column? Is there an appropriate formula?

Thanks in advance for anyone who is able to help!

• Hello Jon,

If I understand you correctly, you can try to use IF along with the VLOOKUP function.

In case you're not sure how to use them exactly, please share a small sample spreadsheet with us (support@apps4gs.com) with your sheets and the example you want to get (the result sheet is of great importance and often gives us a better understanding than any text description). I kindly ask you to shorten the tables to 10-20 rows.

Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying to this comment.

16. Peter says:

I was wondering given your example sheet above.

If I want to count the amount of Dark Chocolate sold fx.

so sum up all numbers in column total sales where column Product = "Dark Chocolate"

• Peter says:

Figured it out ..
=SUMIF(A2:A500,D1,C2:C500)

Where content of D1 would be the value to look for

17. Walter says:

Thank you for monitoring this channel and helping us out.

How can I use COUNTIFS and OR

=COUNTIFS(E35:T35, "Z" OR COUNTIFS(E35:T35, "1" )

18. James says:

Hi I want to count only if two criteria are met.
EG Cells in Range A14:A35 = A and Cells in range C:14:C35 = W

I only want a count of 1 if both conditions are met

Thanks

James

19. Emma says:

Hi there,

I was wondering if there was a way to count if one cell is greater than another range. For example I have a set of scores for students in P column (say range P3:P80) and a second set of score in Q column (Q3:Q80). I want to count the scores in Q column IF they are greater than the initial scores, e.g. I want to count the number of students who got a higher score in the second set of results.

Cheers,
Emma

• Hi Emma,

Here's a formula for you to try:
=SUMPRODUCT(--(Q3:Q80>P3:P80))

20. Colin says:

Hi,

I have this Array Formula in my Google sheet:

={{unique(ArrayFormula(text(K4:K2001,"MMMM")))},
{ArrayFormula(sumif(ArrayFormula(text(\$K\$4:\$K\$2001,"MMMM")),
unique(ArrayFormula(text(K4:K2001,"MMMM"))),\$I\$4:\$I\$2001))}}

The dates it is counting starts from August 2020 through to April 2021.

My problem is for any dates not completed in any given month, it is counted in December, this giving an incorrect count.

Any idea how to fix this?

• Hi Colin,

We tried to recreate the formula on our side and it works correctly. Please go to File > Spreadsheet settings and specify what locale you have selected there. Also, provide a couple of examples of your dates in cells.

I have a COUNTIF formula but after the count I want it to divide the count by 2

=COUNTIF(E9:E755,"Couple")

How do I add to the formula above to then divide the count by 2?

• Hello Marnee,

Here you go:
=COUNTIF(E9:E755,"Couple")/2

22. Denise says:

Hi,

Workbook 1, Sheet "ABCSCHOOL" itemizes a constantly changing number of students per section, per grade.
Workbook 2, Sheet "ABCSTAFF" totals the numbers of students per section for all grades, in order to determine the amount of staff needed per section for the coming year.

ABCSTAFF currently has cells that contain the current formula:

I am using the COUNTIF function in ABCSTAFF to count the number of cells that have a number greater than 0. I am not trying to obtain the sum of the numbers in the cells, but rather the number of cells that contain a number. That formula looks like this: =countif(D4:D10,">0")

What I am trying to figure out is:
1. Is there a formula I can use to count a cell twice if the number in it is greater than 8? For example, Assuming range D4:D10 has 3 cells containing numbers 2, 4 and 5 and another cell containing the number 12, I would like the total count to equal 5 cells, as opposed to 4.

Thank you.

• Hello Denise,

I'm sorry but your task is quite confusing. I just don't see why the count should return 5 if only 1 number is greater than 8, so I'm not sure what to add to the formula. I also don't understand what the cell with the importrange and the count should contain as a result.

For me to be able to help you, please share a small sample spreadsheet with us (support@apps4gs.com) with 3 sheets: (1-2) a shortened copy of your source data (3) the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

• Denise says:

Thank you Natalia.

I shared two workbooks with you (they were sent under a different email address). I greatly appreciate any help you can provide!

• Hi Denise,

Thank you for sharing the files. I entered the correct formulas right under your current ones – in row #13 – and colored cells green. Hope this will help :)

• Denise says:

Thank you! This is exactly what I was looking to do. You are amazing!

• You're most welcome, Denise! :)

23. David Hartman says:

Is there a way to conditionally average values? Say there are text values in column A. If cell is not blank in the row I want to know the value of cell B divided by cell C. And then find the average.

Would this be:
`=AVERAGE(if(A1:A100,B1:B100/C1:C100,null))`

• Hi David,

I believe the following formula would work better:
=ARRAYFORMULA(IF(A1:A100<>"",AVERAGE(B1:B100/C1:C100)))

We have an entire blog post devoted to the IF function, feel free to take a look.

24. poohbear` says:

Thanks! It works!

25. Yhang002 says:

Hi, I need help with Google sheets conditional formatting and formulas. I want to know how to highlight rows in Tab 1 with matching specific and partial keywords from Tab 2? And I need to know how to get the total amount of the highlighted rows in Tab 1, Column G. Please see this sample workbook: https://drive.google.com/file/d/1LtZqlrrBoiDbmSZm-5eA26QXngKsIS0w/view?usp=sharing

I hope you can help me. Thank you.

• Hi,

We have a special blog post devoted to conditional formatting in Google Sheets: it describes how to format cells by the text they contain, how to format entire rows and how to create conditional formatting based on another cell.

Also, there are no standard Google Sheets functions that process only highlighted cells. For such purposes, we have a special add-on – Function by Color. It calculates data based on the font or/and fill color. There's a fully-functional 30-day trial period available so you could try it out and see if if suits your needs. You will find the detailed instructions on how to work with the add-on on its help page.

26. ivan delsol says:

I have two columns of data and I need a to count the number of rows where column A is greater than column B. Can I do that with countif?

• Hello Ivan,

=SUMPRODUCT(--(A2:A100>B2:B100))

27. Reda Albotoush says:

hello,
I am trying to use this code,,,, but to cell A21 then A22 and so on,,,, please how to that
with this code i am using entire A column as a range

If Application.CountIf(Sheets("Sheet1").[A:A], Sheets("Sheet2").Cells(RowNum, "A")) = 0 Then

Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Sheet2").Cells(RowNum, "A").Value

• Hello Reda,

It looks like these are parts of the VBA code for Excel while this blog post is about Google Sheets.
Anyway, we don't help with macros so, for Excel users, my best advice would be to try and look for the solution in VBA sections on mrexcel.com or excelforum.com

28. Brian Shepherd says:

Hi Natalia,

So it is a long record of incidents in which the time is recorded, I want to create a line graph for month by the most common time for example 14:00-14:59 and 15:00-15:59.

My formula is based on all the times for that month (January) on a separate tab, I am then trying to put a formula as follows (using the examples above). >=14:00 but =C107",
'Claims Log'!K2354:K2455,"<D107")

Thanks

• Hi Brian,

Thank you for the description.

I'm afraid I will need to look into your data since this time you introduced another criteria '=C107' and didn't specify what lies in D107.

Please share an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: 1) a copy of your source data with a formula, 2) the result you expect to get.

Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.

Thank you.

29. Brian Shepherd says:

If I have loads of times how can I count up per hour

• Brian Shepherd says:

=countif('Claims Log'!K2354:K2455,"D107")

This is the formula I tried, greater than 00:00 but less than 01:00

• Brian Shepherd says:

Sorry, this is what I tried

=countif('Claims Log'!K2354:K2455,"D107")

• Hello Brian,

1. what do you mean by 'count up per hour'?
2. how do your time units look like?
3. what do you have in D107 exactly?

If you provide examples of a few records you have and what you're trying to achieve, it'll help a lot.

30. Rajashri says:

Hi, want to know the formula . If a student scores 7/7 , then he will be graded A+, if student scores 6/7, then he will get A. Like this. Please help

• Hi Rajashri,

In cases when you have several different outcomes depending on various conditions, you'd better use the IF function for the task.

31. Tony says:

Hello.
If a Cell is "YES", then these cells will be added together. If "NO" or empty cell, then do not add. Here is what I have.
=COUNTIFS(Y10:Y11,"YES")+COUNTIFS(AND(B2="BRAND",B3="Shirt"),ArrayFormula(SUM(COUNTIFS(I10:J293,{"X","Y","Z"}))))

• Hello Tony,

I believe the IF function will do better for your task. Please look through the following blog post to learn how it works: IF function for Google Sheets

32. Jeremy says:

Hello,
Very much appreciate this forum, and the help. G Suite is new to me, especially Sheets. This should be a simple one:

Column B has a list customer names:

company a
company a
company b
company c
company a
company d
company e, etc.

Here's what I'm trying to accomplish:

1. This list is ongoing, it doesn't start in B2 and end in B10 for example, this is a running list. B1 is the title of this column, though.

2. I want to know how many projects we do, via a percentage pie chart, for company a, company b, company c, etc. The pie chart would ultimately show 50% company a, 20% company b, and so on.

3. The formula and chart should allow for new customers to be added; there could be a company f, company g, company h, infinitely.

Any help would be greatly appreciated!

• Hello!
To automatically add new customers to the chart, you can specify a very large data range for the chart. For example, your data is in B1: C10 and the range for the chart is B1: C100. Blank cells are not used in the chart. As soon as you write data into B11 and C11, it will automatically be shown in the chart.

• Jeremy says:

Thanks Alexander. But I still don't know how to do the formula correctly, nor do I know how to do the pie chart correctly either.

• Hi Jeremy,

Alexander described how to create a pie chart in this blog post.
There's no need to create a formula. When creating charts, you refer to the existing tables and adjust the chart settings. Please follow the provided link for details about charts.

33. DM says:

Hello
I am trying to do two things:
1. Count data from a different google sheet and populate it in my data sheet. I did the Countif(importrange("html or url key", "sheet and column"), "input")

It returns a 0 although the column has 215 input. I also tried copying the data into a newly created sheet to avoid authority issues. Still not working

2. On a single row i can have 7 entries. Users can enter any of 5 choices from the drop down. At the end of the day i need to count the occurrences of each entry and populate the last cell on the row with the most occurring value. Example

1 2 3 4 5 6 7 Majority
red blue yellow yellow red yellow white yellow

Here the last cell should populate as Yellow as it occurred the most

I tried creating another set of tables at the bottom with just the count of the colors. I am missing the logic that says take the highest count and return the value of that entry. So i want a logic that realize yellow is highest count and it should populate the word yellow as a result. And then I am just referencing that Majority cell back to the original location at the top of the sheet. Example

Red Blue Yellow White Majority
2 1 3 1 Yellow

• Hello!
To find the most common value in a string, use the array formula:

=ARRAY_CONSTRAIN(ARRAYFORMULA(INDEX(\$A\$2:\$S\$2,, MATCH(MAX(COUNTIF(\$A\$2:\$S\$2,\$A\$2:\$S\$2)), COUNTIF(\$A\$2:\$S\$2,\$A\$2:\$S\$2),0))), 1, 1)

• DM says:

I am sharing the spreadsheet. So different people populate the data. Some sections are divided into 2 and some are not. I need Column L to populate automatically depending on the entries. How do you handle instances where 2 values are entered equally? Or if you have one Fully (100%) and Two (75%) and a three (less than 50%). In this case I want it to default to somewhat. Can I change the text values to percentages so it can calculate the average and give me a text value back?

• Hello!
If two values are entered the same number of times, the one at the beginning of the list will be shown.
If cells in a row are merged, this does not affect the calculations.
To calculate the percentage of occurrence of the most frequent value, you can use the formula

=countif(B3:K3,L3)/counta(B3:K3)

or

=countif(B3:K3,ARRAY_CONSTRAIN(ARRAYFORMULA(INDEX(\$B\$3:\$K\$3,, MATCH(MAX(COUNTIF(\$B\$3:\$K\$3,\$B\$3:\$K\$3)), COUNTIF(\$B\$3:\$K\$3,\$B\$3:\$K\$3),0))), 1, 1)) / counta(B3:K3)

Hope this is what you need.

• DM says:

Thank you, this helped alot. I still need help with the following

1. Count data from a different google sheet and populate it in my data sheet. I did the Countif(importrange("html or url key", "sheet and column"), "input")

It returns a 0 although the column has 215 input. I also tried copying the data into a newly created sheet to avoid authority issues. Still not working

Or just copy the data as it is entered into another sheet. Not tab, a new sheet

34. April says:

I feel I am missing something in my code. perhaps you can help! My intention is that when 2 criteria (column n which holds a tag type and Column m that holds a tag number) both have duplicates it would change the color on column n on the duplicates. I have done this before with one column =countif(B:B,B1)>1 and that works, but trying to do 2 columns it's not working. what am i missing? Two codes below are ones I have used to try to get to work.

=((countif(N:N,N1)>1)+(countif(M:M,M1)>1))

=COUNTifS(\$N\$2:\$N, ">1",\$M\$2:\$M ,">1")

• Hello April,

If you're trying to color complete row duplicates, a formula like this should help you:
=COUNTIFS(\$M\$2:\$M;M2;\$N\$2:\$N;N2)>1

35. Eli says:

Hi,
We're using sheets as a weekly time table for project work management. We write down which project we worked on at what time. I'm trying to summarise the amount of time worked on each project at the end of the month.
we have 2 products that use the same project names, with the product acronym ahead of the project name (IE-AB Cars\CD Cars).
Each sheet represents a week.
Using =COUNTIFS('2-8.8.2020'!A3:G22,"*AB*",'2-8.8.2020'!A3:G22,"*Cars*")
This works fine when I refer to one sheet\week at a time. but trying to add all sheets\weeks doesn't work

thanks

• Hi Eli,

Could you please let us know what your formula returns exactly when it doesn't work? Is it '0' or some specific error?

• Eli says:

Hi Natalia,
It returns'0'

• Thank you for replying, Eli.

Since COUNTIFS "returns the count of a range depending on multiple criteria", '0' usually means that there are no records matching all your criteria.

However, if you see matches without the formula and are sure the result should be different, please share an editable copy of your file along with the formula that doesn't work with us (support@apps4gs.com).
Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying to this comment. We'll look into it and see what may be going wrong.

36. Farah says:

I need some help.
I have a calender with an allocation of 2HC per day to go on leave
I would like the first 2 names that are input in the day to be left in its original format but with every additional names added into the particular date range will be highlighted.

I'm currently using =countif(\$F\$8:\$F\$11,"*")>2 but this will highlight every column in my range for as long as the input is more than 2.

Is there any way to go about highlighting only the names that comes after the 2nd entry?

Thanks ^^

• Hello Farah,

Please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: 1) a small example of your source data with the rule you're trying to apply 2) and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.
Thank you.

37. jacob says:

can we count how many operators does a cell have in spreadsheet

• Hello Jacob,

38. Alek says:

Little help? countifs two arguments don't work for me

• Hello Alek,

We describe how to count by multiple criteria in this part of the blog post.

If your formula still doesn't work, please provide it here so we could see how it looks.

39. Martin says:

Good afternoon, I have a question.
I have several texts in different cells. The texts are similar. How do I know if they are the same? And if they are not the same, how do I know what percentage of words are repeated? And based on that percentage, can I declare a color range?

Then, I need to import from one sheet to another according to that percentage. If the percentage is high, it is not copied, if it is low, it is copied.

thank you... ♥

• Good afternoon, Martin,

I'm afraid your task is rather complex so I can't suggest anything in particular. However, you can share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: a copy of your source data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here. We'll look into your task and see if we can help.

40. Mahesh says:

Sheet 1
Col A1 58+1 BOXN
A2 42+1 BCN
A3 57+1 BCNHL
A4 58+1 BOXNHL
Sheet2
Col A1=( Count of BOX/BOXNHL)[=2]
Col B1=(count of BCN/BCNHL)[=2]
HOW TO WRITE FORMULA

41. Tomi Lehti says:

Is it possible to use countif with ValuesbyColor. IE I'd want to know how many times "John" appears in either red or blue cells

• Hello Tomi,

Yes, you can use the valuesByColor formula in the COUNTIF. However, one formula can process only one color at a time, so COUNTIFS won't work here. Please do the following:

1. Count each color separately.
2. Add each valuesByColor formula for each color into individual COUNTIF formula to count 'John' only:
=COUNTIF(valuesByColor("#f4cccc", "#000000", Sheet1!A1:A10),"John")
3. Sum the results.
• Tomi Lehti says:

Thanks that worked! I also have a follow up, does it work with sumif?
I've tried
=SUMIF(valuesByColor("#e06666", "#000000",Sheet1!\$B\$2:\$B\$999"*John*",Sheet1!\$D\$2:\$D\$1000))
The second range (\$D\$2:\$D\$1000) counts time so it should create how long time John has accumulated in all the cells with that color. But the error it gives me that there is only 1 argument

• Tomi,

The SUMIF function requires different arguments, so placing them in the same order as for COUNTIF won't work. Also, in your formula, you put the condition for John and the range with time inside the valuesByColor, this also shouldn't happen.

If I understand it correctly and you need to sum times based on two conditions: 1) if there's 'John' and 2) if a cell is coloured specifically, it is SUMIFS that you need (sum on multiple criteria). But at the moment valuesByColor doesn't work within SUMIFS and COUNTIFS.

42. MIKE says:

Thank you all for this content. I have one column of data where each cell is assigned a number between 1 and 13. I'm trying to find how to know the probability that any one number follows another. For example, what is the probability that a 7 is followed by another 7. Or what is the probability of the sequence 4, 9, 13. If anyone can help with this formula I would be more than grateful.

• Hi Mike,

I'm afraid this task is rather complex and can't be solved with one simple formula. I'd advise you to look through these tutorial videos provided by the Google for Education service:

43. Regielyn Fajurano says:

I need help for Google sheets formula.

if the percentage value of F4 > C4 = "good"
if the percentage value of F4 < C4 = "bad"
if the percentage value of F4 = C4 = "same"

• Hello Regielyn,

44. Kelsey Hoskins says:

Hi,
I have an 'attendance' list each week. I have names indicated by row, and dates indicated by column. I mark present with an 'x'. I would like to select each week (5 days so 5 columns) for a group of students (31 students, so 31 rows) and see if they showed up at least once that week. I do not want to count if they were there multiple days, but just know how many students out of the 31 showed up at least once that week. Is there a formula for this?

• Hi Kelsey,

You can create additional columns right at the end of each week and use the formula like this for each student:
=IF(COUNTA(\$B2:\$F2)>0,"attended","absent")

Please refer to these articles explaining how these functions work:
IF function
COUNTA function

45. fazlan says:

How to count the Quantity of Milk Chocolates ?

• Hello Fazlan,

the SUMIF function will sum numbers related to a certain text. Please check this tutorial to learn how to use SUMIF: SUMIF in Google Sheets.

46. Hebert says:

Hello!
I need to count "No" values of users on a column, but this count needs to be conditioned by this:
If the "No" value is already on another row of the same column and belongs to the same user (this can be checked because I'm using one app ID on a specific column), then don't count it.
How can I filter that with formulas?

• Hello Hebert,

If I get it right, the quickest and easiest way is to use our Remove Duplicate Rows add-on to find all unique rows and mark them in a status column.
Then use COUNTIFS to count those rows that have "No" as the answer and that are Unique:
=COUNTIFS(B2:B10,"No",C2:C10,"Unique")

47. Pocholo says:

Hi,

I've been trying out the different formulas that seem to apply to my sheet, but it's weird, because the formula works, but the result is wrong. I have column F, which lists down the names of several people repeatedly.

Once someone completes a task in the list, they are to change the cell's background color to Shamrock Green (hex code is #0F9D58).

In a separate sheet for a summary, I have a column that serves to count the total number of tasks that they have completed (marked by the background color). I've been testing it out by coloring the cell accordingly, but the value that is returned is 0.

This formula, which I've learned about from your other article, works fine by itself! But I need to countif the name of the person when the cell has been colored Shamrock Green.

Would appreciate any help! Thanks.

• Pocholo says:

Sorry, here's the formula that I've been trying to use:

• Hi Pocholo,

Unfortunately, since our valuesByColor returns values ​​from cells, it cannot be wrapped in another condition for another column. We'll consider improving the formula in the future if it's technically possible.

For now, I can only offer to try and use Scripts. Here's an overview of Google Apps Script with a lot of helpful content and links:

• Pocholo says:

Hi Natalia,

Got that! Appreciate the response -- just wanted to confirm as well, whether my formula was constructed correctly, or was it even supported by the function, since there is no error message and the cell just returns a 0 value.

Thank you, and more power to your blog! They're quite informative and very helpful when troubleshooting and navigating the nuances between Google Sheets and Excel. :)

• Thank you, Pocholo! :)

• Hi Pocholo,

I'm happy to let you know that we released new versions of our Function by Color and Power Tools add-ons that include two new functions that can be used in COUNTIFS/SUMIFS in order to process cells based on colors & contents.

You will find all the necessary info about these functions in this tutorial.

48. E says:

I am copying some formulas from Excel over to Sheets and one of them just isn't translating.

But when entering the same formula into Google, it returns 4.

I was trying to find out if the syntax is just different in Google Sheets but so far haven't figured it out.
I'm guessing it's in the multi criteria between the "{" brackets... but... can't figure it out.

Thanks

• E says:

Looks like all I needed was to add

ArrayFormula(

To the front of it

• Hello E,

Glad to hear ARRAYFORMULA did the trick! :)

49. osama altawash says:

Hi Guys..
I hope you all doing fine…

I have problem ..

I want to sum (range of number) if the criteria is matches the formula below.
I have multiple ranges and criteria .
And one of the ranges I need to add 2 criteria .

so for example :
I need to calculate the salary for the employees
If the employee (( city) (The range)) is from ((city 1 or city 2)(multiple criterias ))
And other ranges also like ( showroom, age and profession etc) .

• Hi Osama,

We covered how to sum in Google Sheets based on multiple criteria in this blog post, feel free to have a look :)

50. Ryan Smith says:

I try to find the number of cells with a value greater than 30 but it gives me an error. This is the formula I use. Do you know what I’m doing wrong?

=countif(c259:c261,”>30”)

Thanks

• Hello Ryan,

Try using straight quotes instead of smart ones for the condition:
=COUNTIF(C259:C261,">30")

51. Juul Kabas says:

I'm using your formula, but changing the inputs. I'm using a QUERY instead of just a fixed range. For some reason, I have a number of values that result in an empty cell. If I remove the ,) at the end of the IF-formula, I have a number of cells that output FALSE.

Is there a way to get rid of those?

• Hello Juul,

For us to be able to suggest anything, please provide your formula so we could see what you enter exactly.

52. Nicole says:

Hi,
I have a range of data that contains dollar amounts and I am trying to find any amount that is greater than or equal to \$5 and color coordinate it based on how many times it appears in the data set. For example the first time a cell contains data greater than or equal to \$5, it would be color coded as green, the second time it happens, it would be color coded as yellow, etc.

Can you help with that?
Thanks!

• Hi Nicole,

You should put COUNTIF into the IF formula and use both in the conditional formatting.

For example, (assuming your data starts in A2) this is the formula for conditional formatting that will colour the first occurrence of the value that is >=5 in green:
=(IF(A1>=5,COUNTIF(\$A\$2:A2,">=5"),""))=1

To colour the second occurrence, create another rule with another colour and change 1 to 2:
=(IF(A1>=5,COUNTIF(\$A\$2:A2,">=5"),""))=2

And so on.

Hope this helps! :)

53. Parker Lewis says:

I have a spreadsheet where i need a formula to count for a certain value in column B, but if that value is counted, it needs to reflect a number in column S. So if B4 meets the criteria, I need the value of H4 to appear.

Thank you in advance for any help

• Parker Lewis says:

Excuse me, the H4 in the phrase "I need the value of H4 to appear" should be replaced with S4 to match my earlier example.

Thank you.

• Parker Lewis says:

Thank you

54. Sara says:

Hello,

I used the formula =COUNTIF(M3, "*(Pme)*" to tally the number of times that a specific cell contains a coding tag. However, the formula is treating 0/1 as No/Yes instead of giving me a total. Can I adjust the formula to count the number of times the coding tag appears instead?

For example, I have cell M3 which contains the sentence "Also, I (S) know (Pme) plenty of straight guys (Ph) who (S) are attracted (Pme) to more masculine women (Ph)". The formula above produced the number 1, but I want it to produce the number 2 because the tag (Pme) appears twice in this sentence.

Any feedback would be appreciated. Thank you!

• Sara says:

Sorry, I forgot to close the parentheses. The formula is: =COUNTIF(M3, "*(Pme)*").

• Sara says:

Okay, so upon further searching, I found =COUNTA and =COUNTUNIQUE, which yielded the results I wanted, but now I'm wondering the difference between the two. Thank you!

• Sara says:

I take the previous comment back-- these functions did not necessarily fix the issue. Sorry for the multiple posts and thank you for your time!

• Hello Sarah,

none of the functions you mentioned will help: they count cells that contain the word you need, not the number of words within those cells. For your task, you need to use the LEN function like this:
=(LEN(M3)-LEN(SUBSTITUTE(M3,"(Pme)","")))/LEN("(Pme)")

1. LEN(M3) - counts the number of all chars in a cell
2. LEN(SUBSTITUTE(M3,"(Pme)","")) - counts the number of chars without the entry of interest
3. then subtract one from another to find the difference in chars
4. and lastly, divide it by the total number of chars
• Sara says:

55. Dan says:

Hello,
I'm trying to find a way to use countifs based on values being split out of comma separated values, but something isn't quite clicking.
If i have:
ID
1
2
3
and then
SCORE | SELECTION
0 | 1,3
1 | 2,3

and i want to find out the total number of times that ID 3 was selected and there was a score of 0. The result should be 1.
I feel like i'm close with
=COUNTIFS(SPLIT(\$G\$11:\$G\$14,","),3,\$F\$11:\$F\$14,0)
But i get told "Array arguments to COUNTIFS are of different size."

• Hello Dan,

The SPLIT function won't work like this. It's supposed to separate values to multiple cells rather than extract records needed for calculations. Our tech specialist has created the following array formula that should help:
=ArrayFormula(SUM((MID(\$G\$12:\$G\$13,FIND(",",\$G\$12:\$G\$13)+1,1)="3")*(LEFT(\$G\$12:\$G\$13, 1)="0")))

56. Tony says:

Hello,
i use Google Sheet and i would like to calculate how many cells there are in a column of the same color, provided there is a value from another colone. COUNTIF is not good for color

example:
(A2: A100; A15; B2: B11; "Acquired")

• Tony says:

OK, thanks.
but that's not exactly what I'm trying to do.
This is not a COUNTIF but rather a COUNTIFS that I would need.

this example may be better explained.

A B C D
1 | color 1 | Acquired | Total color 1 Acquired | 2 |
2 | color 1 | Blocked | Total color 2 Acquired | 1 |
3 | color 2 | Blocked | Total color 1 Bloqued | 1 |
4 | color 2 | Acquired | Total color 2 Bloqued | 1 |
5 | color 1 | Acquired |

• Tony,

Since there's no standard way of counting cells by their colours, our tool will still be useful.
If I understand your task correctly and "color 1" and "color 2" are cells with backgrounds, you can generate a formula with our Function by colour tool and then use it in your COUNTIFS, like this:

The part in bold was generated by our add-on.
The entire formula counts all cells in column B with a word Acquired if a cell in column A is coloured in accordance with a pattern in valuesByColor.

• Tony says:

OK, thanks.
But I still have a little problem.
I think there is a problem with the third parameter of valueByColor "srcRange", it returns 1 whereas it should send me back 9.
I think that once he has found a good result, he does not continue the loop in the range.

sorry for the double post -_-"

• Tony says:

I tried the method "valueByColor" without going through a COUNTIFS, and I realized that it sends me the values ​​in the cells, but what I want to do is count the number of cells of the same color provided that the cell is acquired (color in colone A, and acquired in colone B).

• Tony,

It's rather difficult to tell why your formula doesn't work. Could you please share your spreadsheets with us to have a look? It's support@4-bits.com. We don't monitor its Inbox, so please confirm by replying here once you share the file. Thank you.

• Tony says:

ok, I just sent you the file with a copy of our conversation.

Thanks.

• Thank you, Tony.

I've got your file and replied to you by email.

57. Casey says:

Hello -
I used your tip to figure out how to count non blank cells in a column on a certain sheet tab (Tab1). This formula works for that: =COUNTIF('Tab1'!Y2:Y889, "")
I wish to further filter the count by a text value in another column. I want to count the non blank cells in column Y that have the value "A" in column N. Then count the non blank cells in column Y that have the value "B" in column N. This formula doesn't work - I am not sure how to connect them. =COUNTIF('Tab1'!Y2:Y889, "") AND COUNTIF('Tab1'!N1:N930,"A") Thank you so much.

• Casey says:

Note the characters between the double quotes in the formulas above are these - not sure why they did not display. If they don't display again in this comment, they are the less than sign and the greater than sign. Thank you again.

• Hello Casey,

You need to use COUNTIFS for the task:
=COUNTIFS(Tab1!Y2:Y889,"<>",Tab1!N2:N889,"A")

Please keep in mind that the function requires ranges of the same sizes. Based on your example, you'll have to enter either rows 2:889 for both columns or 1:930.

• Casey says:

Thank you, Natalia - I thought I might need to use IFS, but I had the syntax garbled. Thank you so much - this worked exactly as I wanted.

• You're most welcome, Casey,

58. Levi says:

We want to count the values between certain dates (for our monthly report).
Our document is structured like this:
A column: Date (eg. 01-01-2019)
B column: Value (eg. Owned, earned or paid)
What's the correct formula to count the number of Owned between 01-01-2019 and 31-01-2019? Any help would be greatly appreciated!

• Levi,

=COUNTIFS(B2:B10,"Owned",A2:A10,">="&DATE(2019,1,1),A2:A10,"<="&DATE(2019,1,31))

59. BILL G says:

I have 4 tabs of information with columns of information that are in drop-down lists. I need to calculate the total number of times each of the items in the drop-down lists is selected (on a 5th tab). I've created the 5th tab and done the "COUNTIF" but it's not totaling. Instead, there's an error.

• BILL G says:

To clarify, this is on a Google Sheet.

• Bill,

will you be able to share your file with us at support@4-bits.com? If so, please reply here once it's shared. We'll look at how your data is stored exactly and try to come up with a solution.
Thank you.

60. Ralph says:

Hi,

Can you help me in getting a formula for a countif with cells containing duplicate texts.
Example:

R1 test
R2 test | test

when i use this; =countif(A:A,"*test*"), i only get 2; but I want to get 3 as the result.

Thanks for the help.

• Hi Ralph,

If your duplicates are divided by the same "|" character, you can try this formula:
=COUNTIF(ArrayFormula(SPLIT(JOIN(" | ",A:A)," | ")),"test")

Otherwise, you can use our Split tool to divide cells with duplicates by their delimiters and then count all occurrences.

61. Teri says:

I want to know the total value of column b if column f is John column h is July

• Teri,

I believe the SUMIFS function is what you need to use for the task. It will sum records of column B based on values in F and H.

62. Jane says:

What if I need a pie chart to show payment

If Chocolate Milk, Count the Total Value of all the orders.

I have a data set where the 'Chocolate Milk' has different values, but I need the formula to calculate all the column A results if 'Cholocate Milk', and take the value from another Column. :-/

• Hello Jane,

To find out the total only for those cells that contain "Chocolate Milk", you need to use the SUMIF function instead. You'll find the tutorial about it on this page.

63. Abhinav says:

Dear Sir ,
I am having a set of data having some duplicate values in it . I wants to mark its frequency in its adjacent column except first one .
like
1
1 duplicate
1 duplicate
2
3
2 duplicate
4
5
2 duplicate

• Hello Abhinav,

I'm sorry, I'm afraid there's no easy formula to do that.

You can either identify duplicates without the 1st occurrences using a formula like this:
=IF(COUNTIF(\$A\$2:\$A2, \$A2)>1, "Duplicate", "")

64. Jeff says:

Hi,

I'm trying to use a cell as the criterion for a countif search, but I can't make it work.

Name Running Count
Joe Smith (=countif(List1!F:F,"*Smith, Joe*")

What I would like to do is be able to have sheets use a cell, so then I could copy the same formula all the way down. Something like =countif(List1!F:F,[text from cell a2]).

Is this possible?

• Jeff says:

Also, I forgot to say that the text from A2 that I will be searching for will be in a cell with other names as well, so I need a wildcard to be able to search withing the cells of names to find the one I want.

So, the cell I'm searching might say "Peterson, John, Johnson, Mike, Thompson, Charles, Smith, Joe, Williams, Sam"

From that I need to be able to search for the name I want, which would be found in cell A2.

I hope that makes sense.

• Hi Jeff,

If you have "Joe Smith" written in A2, but the range to search in contains names like "Smith, Joe", referring to A2 won't give you the correct result. The formula will look for the exact contents of A2.

In order to use A2 as a source cell, you need to extract first and last names and concatenate them separating with a comma. Here's what I can offer to try:

=COUNTIF(List1!F:F,"*"&CONCATENATE(RIGHT(A2,LEN(A2)-SEARCH(" ",A2)),", ",LEFT(A2, SEARCH(" ",A2)-1))&"*")

65. Nickole says:

Hi, I am trying to find the count of TRUE Values and False Values. My data set is from B2:K146. When I do =COUNTIF(B1:K146,"TRUE")I get an incorrect number. When try the same formual with False, I get a HUGE number that isn't even the amount of cells I have filled with either True or False. Any help is appreciated, I have a huge data set full of True/False that I have to parse out and being able to use a formula to figure out how many are true vs false will be a lifesaver!!!!1

• Hi Nickole,

You can try omitting double quotes in your formula since TRUE and FALSE are Booleans that doesn't necessarily require these:
=COUNTIF(B2:K146,TRUE)

Also, make sure to apply the correct data format to your resulting cell: Format > Number > Number.

66. Luciano says:

Hi, I have 3 columns
Column C = Category
Column D = boolean

I have many cateogies
I need to count the amount of rows that has a specific category with TRUE in the column D.
I would need something like this: =countif(C:C, and("C=category1",D=true))

Can you help me? THANKS!
example: count how many CGAs has true should give: 2
C__|D__
CGA|true
VGA|true
CGA|false
VGA|false
CGA|true

THANKS!!!

• Hi Luciano,

=COUNTIFS(C:C,"CGA",D:D,TRUE)

67. Melissa says:

Hello!
I've tried to do COUNTIF for multiple variables, but it's not pulling the correct data.
How do I set up the formula so that both variables in a row are true.
Example data:
Bob, Male, Form 1
George, Male, Form 2
Sally, Female, Form 1
Jo, Female, Form 1
Sue, Male, Form 1
Jane, Female, Form 1
I would want formula to output the numbers that correspond to gender:
Form 1 - 2 Male
Form 1 - 3 Female
Form 2 - 1 Male

• Hi Melissa,

I'm sorry, I don't quite understand your task. If you could create a sample spreadsheet with your source data and the result you'd like to get and share it to support@4-bits.com, I may be able to come up with a solution.
Once you share the file, please let me know by replying here.

68. jack says:

how to filter data or counts the value if a cell contain more than one value which is seperated by commas

• Jack,

in this case, you need to look for partial matches. Please read through this point of the article to check how to do that.

69. Sean says:

You are a life saver, I feel into the trap of =COUNTIF(C6:C16, D6:D16,"Milk Chocolate") and this ironed that out.

70. Andy says:

Is it possible to do a countif of cells based on criteria of a previous cell? For example, I would like to count the number of cells in a row with the number 26, but only if the previous cell in the row is the number 25.

• Sure, here's how the formula should look:
=COUNTIFS(A2:A1001,26,A1:A1000,25)

71. Katherine says:

Hi! Is there a way to exclude qualifications? I have a column with multiple sources listed in each cell "Referral, Job Site, Agency, LinkedIn" and I'd like to only count the ones with the word "LinkedIn" in the cell AND the ones where LinkedIn appears in a cell with other sources too - but as separate counts.

72. Tramy says:

Thanks for the article! I have this range where returns values from another range of data based on criteria, I put iferror to return "" where there's no valid data, then I want to count the cells with valid data returned with =COUNTIF(E3:AI3,"""") but it keeps counting the whole range with "" as well. Can you help me? Thank you!

• Hi Tramy,

I'm sorry I'm afraid I don't really understand the task. You said IFERROR returns "" for invalid data. But then you are trying to count valid data and indicate it as "" as well.
Could you please describe the task in more detail and give the examples of valid/invalid data?

73. Erika Romenesko says:

I am doing a volunteer gig and they are pushing my limits of programming but I am learning so much. I need some help. I have two sheets.
Rating Data: Forces a unique client ID number in column A which is also named range "PTSD_ClientID".
L1 Data: column A, or named range "L1_ClientID" forces validation to enter a value found in column A of Rating Data. It does not enforce unique values.

Here's what I would love some help with. I have a column I in the Rating Data sheet that I would like to display of the count of all entries for that row's client ID (also named range PTSD_ClientID" that is entered in L1 Data.

Any recommendations or tutorials to send me to on how I might achieve this. Thanks in advance!

• Hi Erika,

If I understand your task correctly, you need to use the formula like this on the Rating Data sheet:
=COUNTIF(Sheet2!A:A,\$A1)

where Sheet2 is your L1 Data, A:A is column A on the L1 Data sheet, and A1 is a cell with the unique ID on Rating Data.

74. Alejandra says:

Hello, can you help me with a Google sheet formula to count how many days a student attended tutoring, where:
A1 Last Name
B1 First Name
C1 Date (of the week the student attended tutoring from Tuesday to Friday)

The data contains 100 students where some attended 1 day, some 2+, some 3+, etc.

Thank you very much.

• Hello Alejandra,
If I get it right, you need to add up numbers entered next to the student names. For that, you need to use SUMIF rather than COUNTIF. We have an article about it as well, you'll find it here.

75. Jeffrey S Smith says:

How do I count if for a range of multiple text entries. I.e.

Cell A2 contains the text "dog"

Cell A3 contains "dog, cat, cow, chicken"

I want to perform a count if A3 contains A2 in the string of text.

76. Loren says:

How do I create a formula that counts if one cell is greater than another?

77. mate wierdl says:

I think the post needs to be looked over for typos. I got lost in the very beginning. The post is below. And these are my questions.

Do we count the sales in the West region, not Milk Chocolate?

Why do I put the cursor in C3, which in the middle of the data of the first picture and contains "West" and try to enter the COUNTIF function there?
Where is the range D4:B74 coming from?

"Let's begin from the basics.

We need to count the number of sales in the "West" region. Place the cursor in C3 and enter the equality sign (=). Google Sheet immediately understands that we are going to enter a formula. As soon as you type the letter "C", it will prompt us to choose a function that begins with this letter. And we select "COUNTIF".

The first argument of COUNTIF is represented by the following range: D4:B74. By the way, you don't have to enter the range manually - mouse selection is enough. Then enter a comma (,) and specify the second argument - searching criteria.

The second argument is a value that we're going to look for across the selected range. In our case it's going to be the text - "Milk Chocolate". Remember to finish the function with a closing bracket ")" and press "Enter".
"

• Thank you very much for your attention to detail, Mate!
We changed a few screenshots, but somehow totally forgot to adjust the text, so sorry about that!
Fixed! :)

78. Marcus Smith says:

Hi there,

I've got a COUNTIFS formula which I'm building to try and accommodate variable criteria, i.e. dates.

I want to code a COUNTIF with 'greater than or equal to' to a date cell, but it doesn't seem to want to read that cell. I've tried formatting the date cell in different ways but not working.

For simplicity I've reduced it to a COUNTIF.

None of the following work:
=COUNTIF('Student Tracking Report'!AS:AS,<=D\$5)
=COUNTIF('Student Tracking Report'!AS:AS,"<="D\$5)
=COUNTIF('Student Tracking Report'!AS:AS,"<=D\$5")

This does work, but won't be variable:
=COUNTIF('Student Tracking Report'!AS:AS,"<=43344")

Marcus

• Nick Mikhuta says:

Hi
The solution is pretty easy, you've just missed the "&" symbol before the cell address. Try the formula below and enjoy ;) =COUNTIF('Student Tracking Report'!AS:AS,"<="&D\$5)

BTW, it is in this article in the "COUNTIF Google Sheets for less than, greater than or equal to" part.

79. Teco says:

Any know how to COUNTIF from anothes sheet page into the same file?
Thanks.

• Create the formula on one sheet, but refer to cells from another sheet :)
It will look like this: =COUNTIF(Sheet1!A1:A10,">20")

80. Ferenc says:

Hi,

Could you help me with the following problem:

I want to count the cells in a range (column) which contains any characters inside. Cells has time format, and an 'if' formula: =IF(OR(B10="",D10=""),"",D10-B10-\$M\$4)

I tried with countif(range, "*") -> it interestingly count cells which are empty, contains just the formula, but no value, as the corresponding Bx or Dx was empty. And does not count any which has a value (8:00 for e.g.) out of the formula.

With countif(range, "") I had exactly the same result...

Am I doing something wrong?

81. Doug says:

Inx:
Where Fruit is in F2:F5 and Color is in G2:G5 the formula looks like this in an empty cell:
=COUNTIFS(F2:F5,"Apples",G2:G5,"Blue")

82. Inx says:

Hello,
Could you help me with a formula for the following:
I need a count of the number of people who answered one answer in the first column, who also answered a specific answer in the second column.
Example:
People who choose "Apples" as 'favourite fruit' who ALSO choose "Blue" as 'favourite colour'.

Thank you

83. Shelby says:

I have results from a google form.

In the form responses, I need to separate the data in several ways.

1. Responses/multiple choice answers by Class/Teacher(which is a column/question in the Form) and by the entire school.

2. Each response has been assigned a letter value which is an abbreviation for a response type. (I've already used Find & Replace to change the text answers to the letter value, now I just need to get and graph the counts of each of the response types.)

3. Responses by student.

There has got to be a way to separate into three additional spreadsheets using the information from the initial form response spreadsheet.

84. Rick Prescott says:

What if I need to match a text with case sensitivity? Looks impossible with this formula, but probably shouldn't be.

• I believe you'll have to use the SUMPRODUCT function along with EXACT.

85. Luke Douglas says:

I have a column of dates (B3:B2) and I wanted to get a count for the following criteria:

Under 2 years
2-5 Years
6-10 Years
Over 10 years

I have used this for the under 2 years and it is not returning the proper number:

=countif(B5:B53,"<today()-730")

I know this is very old, but it might help someone else.

I was trying to build a very similar formula, and I also was not having any luck. However, I found someone else mention that you can 'combine' formulas by using the & sign, so I tried this and it worked.

=countif(B5:B53,"<" & today()-730)

Apparently the today() function shouldn't be in quotes.

86. Nicholas traynor-richardson says:

Hi,

I'm looking for a formula for 4 different countries (Ireland, Denmark, UK & Sweden) so I can keep track of my days in a country throughout the year. I'm using the below,

=COUNTIF(W3:W30,"Ireland")+COUNTIF(W3:W30,"Denmark")+COUNTIF(W3:W30,"UK")+COUNTIF(W3:W30,"Sweden")

I've tried using *UK* etc but not working. I have this formula in another column and it works fine?? Help :)

87. Sami says:

Hello

I need some help in writing a query/script for excel/googlehseets that will take a word and link/URL from where I want to find the word then the query/script will count the number of repeating time of given word and place that number back into excel/googlesheets.
Anybody can help me in this.

88. Lyf says:

So I have a sheet where I want to track how many participants answer certain questions. There are 11 questions and each question has its own reference cell. The cell beside the participant will have something like 1,2,3,7,9 in it, but I can't figure out how to count only one of those numbers while excluding the rest. Any suggestions?

• Doug says:

Just to clarify: are the respondents in the first column, the response to the secomd question in the secomd column, and so on for each of the eleven questions?

• Lyf says:

So, the columns and stuff would look like this

NAME QUESTIONS ASKED QUESTION # of PARTICIPANTS
Joe D 1,2,3,7,9 7 [insert formula here]

(Does that layout make sense?)

• Lyf says:

(oh man...I just looked at how my reply was formatted...it looks awful. Can you follow it at all?)

• Lyf says:

A coworker of mine helped me figure it out: =COUNTIF(B\$2:B\$30, "*"&I2&"*")
Putting the cell that contained the number I was trying to find in between "&"'s and putting the wildcards in ""'s joins it to whatever before and whatever after. This is SO INCREDIBLY helpful! posting it here for all to see :-) this means you can have multiple variables in the same cell and still be able to parse that data!

89. Ondra says:

Hello,

could anyone help me with the following task? My dataset contains (among other) these columns: (A)Date of the 1st check, (B)date of the 2nd check, (C)Date of the 3rd check and (D)Status. Let's say Status could be "Waiting", "Not OK", "OK". At the end of the day I need a number of cases, where Status="OK" and at least one of the Checks (1st, 2nd, OR 3rd) were done today. If there were just one check, I would use: countifs(D:D;"OK";A:A;"26.02.2018"). But I am not sure how to solve out the task, where one of the required criterion (today's date) can be met in one of the several columns.

Thanks a lot

• Rich says:

Greetings, Ondra!
Simply modify the single-column version of your formula to include the other columns.
=countifs(D:D;"OK"; A:C;"26.02.2018")

To make the formula a bit more versatile, you can replace the hard-coded date with, for example, the TODAY() function or a cell reference that contains a date.
=countifs(D:D;"OK"; A:C;today())
=countifs(D:D;"OK"; A:C;E2)

To be really cool, you could use something like the last formula, then find items checked today by entering
=today()
in cell E2. :-)

90. Mike says:

Just wanted to say thank you. This was the clearest depiction of how to reference other cells within a COUNTIFS formula that I could find.

91. JANHO says:

HOW TO COUNT HOW MANY CUSTOMER ORDER MILK, DARK ETC. THANKS

92. Martin says:

Hey,
could you give me a hint how to use countifs with the following table which contains results of different soccer games;

as an example
A1:A5 Name Player 1
B1:B5 Goals Player 1
C1:C5 Goals Player 2
D1:D5 Name Player 2

Now, I wanna count the wins (more goals) of a specific player:
=countifs(A1:A5, "Player1", B1:C5, (B1:B5 > C1:C5))

Unfortunately, the second part throws an error :(

• Hey, Martin,

=SUMPRODUCT((A1:A5="Player 1"),(B1:B5>C1:C5)

If it doesn't work, please go to File > Spreadsheet settings > Calculations, and make sure that Iterative calculation option is On.

Hope this helps!

93. John says:

Is there a way to use =countif to count if there is any value in the identified cell/cells? For example:
| | -should not count
|a | -should count
| | -should not count
|ab| -should count

94. Rachel says:

Hello!

Is it possible to use COUNTIF to determine how many cells do not contain a particular letter? I understand how to use the formula when dealing with numbers, but mathematical symbols don't seem to work the same way with letters.

So, for example, if I want to know how many cells in A1:A25 do NOT contain G, how would I write that?

Thank you!

• Hello,

=ArrayFormula(SUM(IFERROR(FIND("G",A1:A25)=0,1)*1))

I have the same obvious issue as Rachel. I want to count cells which are not "N".
Thanks,

For some reason, Gennady's formula wasn't displayed. I've edited the comment and now you can see the formula. Please try to adjust it for your data.

95. Jess says:

in google sheets, i'm trying to count the number of occurrences where column W=YES, IF column P=5. How can I write this formula?

• Hello, Jess,

you can try the following formula:
=COUNTIFS(W:W,"YES",P:P,5)

96. Patrice says:

Is it possible to use, as a criterion, the background color of a cell in the countif function? For example, in column A (say from A5 to A10), there are two types of cells: those that have a green background color and those that are white. The range of cells to be added is in column B (say B5 to B10). I would like, in cell B11, to sum cells B5 to B10 if they are next to green cells. In cell B12, I would like to do the same for the cells next to the white cells.
I thought using Apps script, but I would prefer to use simple formulas.
Thank you.

• Patrice says:

[resolved]Finally, I decided not to use the background color as a criterion for selecting the cells to be added. I found a simpler solution, based on the content of the cells using the sumif function. Specifically, I used the following formula in cell C268:
SUMIF (\$A5: \$A267; "* (AFR)"; C5: C267)
In other words, instead of using the color, I select the cells whose content ends with the 5 characters: (AFR)
Merci

97. Mary says:

What is the countif formula in goggle sheets if the possible choices included are as follows: >, <, or =. I have used the following formula and keep getting an answer of 0 students with the correct answer. However hundreds of students have the correct answer.
=countif(BL3,"<")

• Hello, Mary,

If I understand your task correctly, there's something missing from your formula. You need to count if one value is greater/less than or equals to some other value. Also, please make sure to indicate the range of cells correctly in the first argument.

So, if your data is spread across B1:L3 and you need to count all the values that are less than 3, your formula should look like this:
=COUNTIF(B1:L3,"<3")

98. Lisa says:

I'm trying to search for the number of occurrences on a particular date. The info in the cell is the data and time stamp. How would I set up the formula so it is only pulling the date?

• Hello, Lisa,

supposing, that the date an time are in A1, use the following formula in the cell where you want to return the date, A2 for example:
=DATEVALUE(A1)

Then select A2, and in the menu choose Format > Number > Date. You will see only the date from A1 in this cell.

Hope this is what you need!

99. loise says:

what is the formula for.

if a1 is less than b1, put good
if a1 is greater than or equal to b1, put bad
if a1 is less than b1 but greater than c1, put critical

put this in one formula?

• Hi, Loise,

try this formula:
=IF(and(A1<B1,A1<=C1), "good", IF(AND(A1>B1,A1=B1), "bad", IF(AND(A1<B1,A1>C1), "critical","")))