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! :)
387 comments
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
Hello Mahesh,
We described how to count cells that contain certain text in the following part of the blog post: Google Spreadsheet COUNTIF function and wildcard characters (partial match)
Your formulas may look like this:
=COUNTIF(Sheet1!A:A,"*BOX*") for A1 and =COUNTIF(Sheet1!A:A,"*BCN*") for B1
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:
=COUNTIF(valuesByColor("#f4cccc", "#000000", Sheet1!A1:A10),"John")
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.
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:
Calculate Probability with Google Sheets
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,
The IF function will help you with this task, please read about it in this blog post.
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
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.
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")
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.
=COUNTIFS(F:F,valuesByColor("#0F9D58","#000000",TASKLIST!F:F),F:F,"NAME")
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.
=COUNTA(valuesByColor("#0F9D58","#000000",TASKLIST!F:F))
Would appreciate any help! Thanks.
Sorry, here's the formula that I've been trying to use:
=COUNTIFS(TASKLIST!F:F,valuesByColor("#0F9D58","#000000",TASKLIST!F:F),TASKLIST!F:F,"NAME")
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:
https://developers.google.com/apps-script/overview
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.
I am copying some formulas from Excel over to Sheets and one of them just isn't translating.
=SUM(COUNTIFS(A21:A93, {"*Contacted*","*Intro*","*Webinar*","*Disclosed*","*Application*","*Territory*"}, B21:B93, "*Business Professional*"))
The answer should be 16
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
Looks like all I needed was to add
ArrayFormula(
To the front of it
Hello E,
Glad to hear ARRAYFORMULA did the trick! :)
Hi Guys..
I hope you all doing fine…
I have problem ..
In google sheet,
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) .
Thank you in advance ..
Hi Osama,
We covered how to sum in Google Sheets based on multiple criteria in this blog post, feel free to have a look :)
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")
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.
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! :)
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
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.
Another clarification, this spreadsheet is on google sheets.
Thank you
Hi Parker,
I believe it's VLOOKUP you should use, not COUNTIF. VLOOKUP searches the info in one column and pulls related data from other columns.
Svetlana explained everything about VLOOKUP in a special tutorial, please have a look:
https://www.ablebits.com/office-addins-blog/vlookup-google-sheets-example/
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!
Sorry, I forgot to close the parentheses. The formula is: =COUNTIF(M3, "*(Pme)*").
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!
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)")
That fixed my spreadsheet, thank you so much for your help!
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")))
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")
Thanks for your help. =)
Hello Tony,
We covered counting by colours in another article, please have a look:
https://www.ablebits.com/office-addins-blog/count-colored-cells-google-sheets/
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:
=COUNTIFS(A2:A6,valuesByColor("#d9ead3","#000000",Sheet10!A2:A6),B2:B6,"Acquired")
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.
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 -_-"
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.
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.
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.
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.
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,
Glad I could help! :)
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,
Please try this formula:
=COUNTIFS(B2:B10,"Owned",A2:A10,">="&DATE(2019,1,1),A2:A10,"<="&DATE(2019,1,31))
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.
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.