by 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:
- 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.
Google Spreadsheet COUNTIF in practice
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.
Google Spreadsheet COUNTIF for text and numbers (exact match)
Let's suppose your company sells various types of chocolate in several consumer regions and works with many clients.
This is how your sales data look like in Google Sheets:
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.
Tip. To count words and characters in Google Sheets, visit this tutorial.
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(E6:E16,125)
or by replacing it with a cell reference:
=COUNTIF(E6:E16,A3)
Google Spreadsheet COUNTIF function and wildcard characters (partial match)
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 part of its name: enter "?est" into A2. 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 B2:
=COUNTIF(C6:C16,A2)
As you already know, the formula can easily take the next form:
=COUNTIF(C6:C16, "?est")
And we can see 6 sales in the "West" region.
Now let us employ the B3 cell for another formula:
=COUNTIF(C6:C16,A3)
What is more, we'll change the criteria to "??st" in A3. 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 9 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:
=COUNTIF(D6:D16,A2)
"*Chocolate" in A2 to count all the products ending with "Chocolate".
=COUNTIF(D6:D16,A3)
"Chocolate*" in A3 to count all the products starting with "Chocolate".
And, as you may have guessed, if you enter "*Chocolate*", you'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, to look for the values that contain "?", the formula will be:
=COUNTIF(D6:D16,"*~?*")
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(F7:F16,">100") |
Count cells where values are greater than 100. |
| The number is less than | =COUNTIF(F7:F16,"<100") |
Count cells where values are less than 100. |
| The number equals to | =COUNTIF(F7:F16,"=100") |
Count cells where values equal to 100. |
| The number is not equal to | =COUNTIF(F7:F16,"<>100") |
Count cells where values are not equal to 100. |
| The number is greater than or equal to | =COUNTIF(F7:F16,">=100") |
Count cells where values are greater than or equal to 100. |
| The number is less than or equal to | =COUNTIF(F7:F16,"<=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 me reference A2 and put the formula in B2, just as I did before:
=COUNTIF(F7:F16,A2)
To create more sophisticated criteria, use an ampersand (&).
For example, B3 contains a formula which counts the number of values greater than or equal to 100 in the E7:E16 range:
=COUNTIF(E7:E16,">="&A3)
B4 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(E7:E16,A4&A3)
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 E are bigger than in the same row of column F:
=SUMPRODUCT(--(E6:E17>F6:F17))
- The part at the core of the formula — E6:E17>F6:F17 — compares values in columns E and F. When the number in column E is greater, the formula takes it as TRUE, otherwise — FALSE.
You'll see that if you enter the same into the ArrayFormula:
=ArrayFormula(E6:E17>F6:F17)
- 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 E is greater than F.
Google Spreadsheet COUNTIF with multiple criteria
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:
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(F5:F16,">=200",F5:F16,"<=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:
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(D5:D16,F5:F16,">=200",F5: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 B2:
=COUNTIF(D6:D16,"*Milk*") + COUNTIF(D6:D16,"*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:
=COUNTIF(D6:D16,A2) + COUNTIF(D6:D16,A3)
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(F6:F16,"<=400") — COUNTIF(F6:F16,"<=200")
The formula returns the number of sales more than 200 but less than 400.
If you decide to reference A2 and A3 that contain the criteria, the formula will be a bit simpler:
=COUNTIF(F6:F16, A3) — COUNTIF(F6:F16, A2)
A3 cell will have "<=200" criteria, while A4 — "<=400". Put formulas into B2 and B3 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(F6:F16,"<>")
or
=COUNTIF(F6:F16,A2)
To count the number of empty cells, make sure to put the COUNTIF formula in the following way:
=COUNTIF(F6:F16,"")
or
=COUNTIF(F6:F16,A3)
The number of cells with a textual value is counted like this:
=COUNTIF(F6:F16,"*")
or
=COUNTIF(F6:F16,A4)
Screenshot below shows that A2, A3, and A4 cells include our criteria:
Thus, I have 4 closed deals, 3 of which were paid for and 7 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$8:$B$38,B8)/COUNTIF($B$8:$B$38,"*")>0.3
It means that the condition will be answered if the value from B8 appears within B8:B38 in more than 30% 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 20%:
=COUNTIF($B$8:$B$38,B8)/COUNTIF($B$8:$B$38,"*")>0.25
=COUNTIF($B$8:$B$38,B8)/COUNTIF($B$8:$B$38,"*")>0.2
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 C2:C5 using the COUNTIF function. The results confirm that COUNTIF in formatting rule was applied correctly.
Tip. Find more examples on how to count & highlight duplicates in Google Sheets.
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.
COUNTIF color in Google Sheets
One task that gains popularity is counting cells by their color. Though there's no native Google Sheets function to do that, you can still achieve the result without coding. All you need is an add-on from the Google Workspace Marketplace: Function by Color.
Using the add-on, you will:
- Count cells of any particular font or fill color in your Google Sheet:
- Count all colored cells in the range:
If you're interested in how it works, jump to this article for the details: How to count cells by color in Google Sheets
- Another ability you gain with Function by Color is using colors as extra conditions in your own COUNTIFS (and other) formulas:
The following blog article breaks down this usage to pieces: Functions for Google Sheets to work with colored cells
Video: How to count colored cells in Google Sheets
Install Function by Color from Google Store to follow the examples along or to try it on your own cases. Let me know how it goes in the comments section below! :)
391 comments
I have a countif summing across 3 columns...into a single cell say A1. Now I need it to go down the rows and add the counts into the same single cell say A1.
sample countif:
=COUNTIF('AMER Exec Summary'!O3:O30, "*complete*")
I know I could keep manually expanding the formula to include all the rows (28 and expanding but that will be a very long formula...
Suspect it may need some soft of array?
Hello Marc,
If your goal is to change the range in one and the same cell (A1), you're right, you need to do it manually. Or, if that's ok for your task, you could use an extra column, add absolute cell references to your formula so certain parts don't change and copy the formula down the column. Then reference different cells from this column in your A1. Furthermore, you could even use COUNTIFS to test some additional criteria and return a certain count based on that.
Hi!
I'm trying to highlight rows using conditional formatting for students based on how well they're currently doing in all their classes.
If they have less than 70% in one class, I want to highlight that row in yellow.
If they have less than 70% in 2 classes, I want to highlight both rows in orange.
If they have less than 70% in 3 or more classes, I want to highlight all those classes in red. (don't highlight the classes they're passing)
Each student has five rows for five classes. Their name and ID are in all the rows.
Column A is their name
Column B is their unique student IDs
Column C is the course name
Column D is their grade out of 100%
I setup conditional formatting to highlight red in A2:D26 for this:
=COUNTIFS($D23)
but it's invalid. :(
All I have so far is conditional formatting to highlight yellow in A2:D26 for this:
=$D2<70
But this isn't helpful because we have 2000 students at our school.
We want to reach out to the students highlighted red that are failing 3 or more classes first.
Hi Emma,
I'm sorry but the COUNTIFS formula you provided here looks incomplete. Please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) and let me know where your conditional formatting rules are set up. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.
Also, please include a second sheet with the result you expect to get. I will look into it and try to help.
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.
Hi Emma,
I've just found your spreadsheet :)
I've created all three conditional formatting rules on the 'source data' sheet, please take a look :) The correct COUNTIFS formulas look like this:
=AND(COUNTIFS($A$2:$A$26,$A2,$D$2:$D$26,"<70")=1,$D2<70)
Feel free to read more about COUNTIFS for Google Sheets in this part of the article.
Hello,
I'm wanting to make the cells blank using the countif formulas instead of having a page full of "0". I am curranty using the following formula =COUNTIF(JAN!B:B,A3) I have tried putting (,"") at the end of the formula, =COUNTIF(JAN!B:B,A3,"") or =COUNTIFS(JAN!B:B,A3,"") but it is giving me a error. Not sure if I'm using the correct formula for what I am wanting to do.
Hello BRENDA,
If I understand your task correctly, you will need to incorporate the IF function for the job. Here's an example of how your formula may look like.
How can I use COUNTIF with a query? I'm wanting to count the number of things within a certain range of a database.
Hello Kate,
Sorry, it's hard to suggest anything in particular as your task is not quite clear. Please describe it in more detail, I'll try to help.
In the meantime, feel free to look through this article devoted to QUERY, you may find it helpful. The select, order by, label & format clauses support the count() function.
Hi,
I want to make count for multiple factors from different tabs.
For example this is working formula for one tab: =COUNTIFS('TEST MASTER TRANSFER'!F:F,"=Adriatic",'TEST MASTER TRANSFER'!A:A, "=06/05/23",'TEST MASTER TRANSFER'!B:B, "=08:20") , and I want to add all same criteria from 5 more tabs? I do not know is it possible.
Hi Goran,
If you need the total of the counts from several sheets, simply add two different COUNTIFS functions in SUM:
=SUM(COUNTIFS(...),COUNTIFS(...))
Hello everyone,
How to count Males and Females with unique names?
Hello Danilo Tullas Bajit,
Please describe your task in detail, I'll try to help.
Hi there,
My spreadsheet has multiple rows that I would like to count, but they are separated by other text. In that situation, how would I format the =countif command?
For example, I'd like to count rows B20:C22, B43:C45, and B67:C69, what would I put in?
I tried this but to no avail =countif(B20:C22, B43:C45,B67:C69, "give and receive meaningful feedback")
Thank you in advance.
Bronson
Hi Bronson,
You can use several COUNTIF formulas as arguments of the SUM function.
Hi Natalia,
I tested out the sum function to include multiple ranges but it doesn't seem to count the numbers properly.
=countif(sum('Grade 7'!B20:S22,'Grade 7'!B43:O45,'Grade 7'!B67:O69),"give and receive meaningful feedback")
I get "0" when there should be "3"
If I just do one range: =countif('Grade 7'!B20:S22,"give and receive meaningful feedback")
I get the correct number of "3"
Am I writing the formula incorrectly?
Hi Bronson,
Sorry if my suggestion wasn't clear. I actually meant using COUNTIF inside SUM, like this:
=SUM(COUNTIF('Grade 7'!B20:S22,"give and receive meaningful feedback"), COUNTIF('Grade 7'!B43:O45,...), COUNTIF(...))
In these above example, If I was to want to find the total quantities for Milk choc and Dark chocolate how would I program that?
Hello Karen,
If I understand you correctly and you need to sum the qty based on the item, please use the SUMIF function instead.
Hello- How can I count the number of times a value appears in a range but only the last n rows? I have a range A1:E1000 that data gets added to periodically. I want to find out how many times a number appears in the range, but only in the last 10 rows. Thank you for any info.
Hello Jason,
Supposing you're trying to count cells with A in the last 10 rows of the column D, here's the formula:
=COUNTIF(OFFSET(D1:D,COUNTA(D:D)-10,0,10),"A")
What if cells could have text that's identical to something longer (e.g., "Chocolate" by itself) that should be treated separately? For example, let's say I don't want a countif of Chocolate to include cells with anything before or after it, meaning it would show a count of 0 using the data in the example.
Also, could it be set up (maybe using a countifs with "or" logic) to look for desired text in one of two possible columns per row without counting it twice if it's in both? Let's say there's a second column that could also have Chocolate or maybe something else, and I want to know how many rows it's true for rather than how many total occurrences.
Hello Roger,
To count cells with the exact text (e.g. only Chocolate) you simply put it to the formula as shown here. "Milk Chocolate" counts milk chocolates. Use "Chocolate" to count cells that have nothing but Chocolate.
As for 2 possible columns, you can try something like this:
=COUNTIF(A1:A10,"Chocolate") + COUNTIF(B1:B10,"Chocolate") - COUNTIFS(A1:A10,"Chocolate",B1:B10,"Chocolate")
Hi
I want to know the total value of column C, but only where the corresponding cell in column B says 'coffee'. Can you help?
Hi Beth,
I believe SUMIF will help you with this task. These instructions will help you build the formula.
this worked for me =COUNTIF(D6:D16,"Milk Chocolate")
But google changed from , to ;
Hello Jeff,
This is perfectly fine. Google Sheets uses different delimiters based on your spreadsheet locale.
This is based off a True of False Checkbox Statement.
I've figured out how to get a number value out of the system.
=sumif(H13:H1000,True,M13:M1000) which gets me the value.
I originally have an item, I put the value in the OnHold Column.
I go back to the same location and discover that the item is now missing.
I move that value over to the Can't Find Column.
I've figured out how to count the number of instances I've made these movements.
COUNTUNIQE replace SUMIF for the number of instances, if I separate everything
=COUNTUNIQE(H13:H1000,True,M13:M1000) , counts the number of instances it's found in this move.
Now, What I'm Trying to do is CountUniqe with a third variable.
=COUNTUNIQUE(H13:H1000,TRUE,O13:O1000,"ITEMA",M13:M1000) : ITEMB, ITEMC
No matter the Item, it returns the same value.
What am I doing wrong?
Hello Mamoru,
Please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) highlighting cells with formulas and adding the expected result if any.
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.
I'll look into your task and try to help.
Hi! I am trying to count the # of cells that have data in column F "" for a specific range determined by data in column A. Specifically- how many unique books in a certain category sent to a specific location. Column A is the category of book (picture book, graphic novel, fiction) and column F is a school name. Column F contains data for number of copies sent. I do not care about the value of column F (ie number of copies)- just how many entries there are in column F for range of column A. How many unique fiction titles were sent to Central High. Number of items in the category is constantly changing, so rather than determining range (A2:A45) I would rather specify the range based in content of column A "fiction".
Hi Sunny,
I'm sorry, I'm a bit confused by the description. For me to be able to help you better, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get. The result sheet is of great importance as it gives 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 and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.
I'll look into it. Thanks!
Hi,
I am trying to count how many cases per staff member against certain statuses. e.g. how many cases does person A have with the status App Taken, Approved, Submitted etc. what formula will I use? Names are in column C and the statuses in Column J. I tried countifs but it does not seem to work.
Any help will be much appreciated.
Hi Dhaval,
If you need to count cases based on multiple criteria, COUNTIFS is exactly the function you need. Please provide the formula you tried that doesn't work for you, I'll try to help.
Hi Natalia,
The formula is =arrayformula(sum(countifs(C8:C396,">=C436",J8:J396,B436,B437,B438,B439,B440)))
where column C has the name of the employee and column J has the criteria like approved, submitted etc. I need the formula to give me a count of how many active cases are there in that range for each empolyee.
Thanks
Hi Dhaval,
Thank you for the formula.
If I understand it correctly and those cells in column B contain statuses, here's how the correct formula should look like:
=ArrayFormula(SUM(COUNTIFS(C8:C396,">=C436",J8:J396,OR(B436,B437,B438,B439,B440))))
If, however, this doesn't work as well, please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) and specify where the formula is. I'll look into it.
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.
Hello!
I am trying to track attendance call offs within a 90-day period prior to today.
Row 8 lists dates from 12/1/2021 - 12/31-2022.
Row 9 lists Call Offs ("CO").
I am playing with the formula: =COUNTIFS($BW8:$RB8, "=TODAY()-90", $BW9:$RB9, "CO")
However, it is returning "0" even though I have tested "CO" within the 90-day range.
Am I on the right track? I'm not sure what I'm missing!
Hello Kelley,
Your formula searches all "CO" dated exactly 90 days ago. If your value should fall between two numbers (today and 90 days ago), you need to build a formula like this:
Count in Google Sheets with multiple criteria — AND logic
Here's a ready-made one for you to try:
=COUNTIFS($BW8:$RB8,">="&(TODAY()-90), $BW8:$RB8,"<="&TODAY(), $BW9:$RB9, "CO")
Ha! Thank you so much! It's working perfectly! :)
You're welcome, Kelley, glad I could help! :)
Can you use COUNTIF to rule out options based on data in another column of the text. For example, lets say column a shares the type of chocolate purchased (dark, milk, white, etc) and column b has the state it was purchased in.
Can COUNTIF tell me how much milk chocolate was purchased in Ohio?
Hello Paul,
Yes. Use the COUNTIFS function for that.
Hi! What COUNT formula should be used to count cells that have different texts or values? For example, there are 15 cells and most common word/value is "Order" but I want to count the other cells that have no value "Order".
Hi Mark,
Use the following formula to count cells without the word 'order' in them:
=COUNTIF(A2:A50,"<>*Order*")
Hello,
I am trying to use a COUNTIF formula concatenated with text and the percentage is formatting as a 15 digit number. How to I adjust the formula to make the percentage show as '0%'?
My formula: ="Seen "&COUNTIF(B2:B,True)/COUNTA(B2:B)
The answer: Seen 0.219178082191781
Thank you!
Hello Allison,
You can decrease and increase decimal places using the options from the Google toolbar. Please see this tutorial for details.
Thank you for your response Natalia, however, I had already tried adjusting the decimal that way with no success. I'm assuming it's because my percentage formula has text with it?
Thanks!
Hi Allison,
Yes, the text string will always make any cell look like text for Google Sheets.
Can you COUNTIF by format, like bold and italics? Like count all cells that are bolded/italicized?
Hi!
Standard Google Sheets functions cannot determine the font format in a cell. You can get various information about a cell using the CELL function. The blog post is about Excel, but you can try applying the same in Google Sheets.