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 sheet for my students submitted in my Academy, some of them joined the academy, while others didn't (not all students submitted the form join the academy after the trial class).
I have a column that shows the state of students which consists of a list of two options (Joined, Didn't join),
I have another column that shows the date the student submitted.
Now, I want to count how many students joined each month (not just submitted).
For example, in month 1, I can use this formula:
=COUNTIFS(F5:F,">=1/1/2024",F5:F,"<=1/31/2024")
But this shows all who submitted on this month,
My question is, how can I make this based on the state of another column that shows the student joined or not (to exclude the students who didn't join)?!
I hope my question is clear and you can help!
Thanks!
Hello Luqman,
If I understand your task correctly, you just need to add one more column (the one with submitted statuses) and one more condition ("Joined") into your COUNTIFS.
In a list of dates:times I would like to count all the cells with Hour=10, and all the cells with Hour=11, and Hour=12 (noon), and Hour=13 (1pm) and so forth.
The goal is to plot the number of calls received during each hour of the work day in a data set spanning a year.
Of course, one can use the Hour() function on the date/time column to produce another column with the hour of the day extracted from each date cell, and then use countif() on the column of hours.
Can I avoid this intermediate step? Thank you for your thoughts on this problem.
Hello Jerry,
If I understand your task correctly, you can just wrap the HOUR function in COUNTIF:
=ArrayFormula(COUNTIF(HOUR(A2:A100),10))
Is there any way I can make it so it imports from sheet 1 to sheet 2? I keep the names in row I and then each time it sees a certain name in row I, can it add up all the numbers in row D that share a row with their username?
So if they have their name in row 1,3 and 5 it will add up D in sheets 2?
Hello,
If I understand your task correctly, you can use QUERY just like in this article or the Combine Duplicate Rows add-on described there at the end.
Hi there. Thanks for your clear explanation. My case is that I want to count the attendance. Sometimes students are absent AM = 0.5 day or PM = 0.5 day. When I input - it is 0.5 but the system count it as 1 day. I have 3 columns. A for absence whole day, AM = 0.5 and PM 0.5. e.g. one student was absent 1 full day and 1 AM and 1 PM. Total count is 2 full days. But, the system takes it as 1, instead of 0.5. I am unable to calculate exact absent days as sheet kept counting it is total 3 days. Please help. =countif($C4:$AG4,"AM") or =countif($C4:$AG4,"PM") or =countif($C4:$AG4,"0.5"). NOt sure I made myself clear. Thank you.
Hi Minny,
Thank you for your feedback :) For me to be able to help you, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data with the formulas you tried (2) 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 and don't monitor its Inbox. 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.
THANK you! I've been trying to use COUNTIF to check for 2 different texts within the same range and this is the first article to nail it.
You're very welcome, Erin! We're delighted that our article proved helpful 😊
plz helpmme out for this......
=COUNTIFS(C14:C44,">=350000",C14:C44,"=350000",C14:C44,"=400000",C14:C44,"<450000")*150
it not working
Hello NAEEM,
It seems like you want to count the number of cells in the range C14:C44 that meet multiple conditions and then multiply that count by 150. Your formula has a small logical issue because it combines conditions using AND logic, and as a result, it's unlikely that a single cell will meet all those conditions simultaneously.
If you want to count cells that meet any of the specified conditions, you should use the OR logic instead.
i m using google sheets for my own bussiness.....
for my monthly income with daily rutine checking from >=3,50000 to =350000",C14:C44,"=4,00000 to =350000",C14:C44,"=400000",C14:C44,"<450000")*150 ( not working )
plz any one can help me out for this.....!!!!!
We are using a google sheet to check in students. From this sheet I have columns for the following:
Wait Time, Check in Time, Student First Name, who they need to see with a drop down of choices, Staff initial for check in, meeting type (drop down), mode (In-person, Zoom, Phone, or email drop down), staff initial when meeting is done, end time, and total time. I have the following formulas set up in the following columns to provide the following.
Column A: Wait time: =I3-B3
Column B: Check in Time: =IFS(E3="","",B3="",now(),TRUE,B3)
Column I: Start Time: =IFS(J3="","",I3="",now(),TRUE,I3)
Column N: End Time: =IFS(M3="","",N3="",now(),TRUE,N3)
Column O: Total Time: =N3-I3
I would now like the formula in columns N and O to only use the formula above when Column L: (the mode of the meeting) is selected as "In-person" or "Zoom" only.
Hello Melanie,
If I understand your task correctly, you need to IF along with AND to combine conditions.
This might not work but I have a merged cell (P10:T47) to where we are inserting text. From that text I have 5 words in Q3, Q4, Q5, Q7 and Q8. In front of them (P3 etc) I need a count how many times the word from Q3 appears in the merged cell. I have tried many things and slightly going nuts. I tried countif and countifs but not working I guess because of the merged cell.
Hello Auni,
For me to be able to help you, please give me an example of how exactly you enter text in your merged cell. I mean, is it something like "cat;dog;parrot"
or
"cat
dog
parrot"
or else?
Hai!
Saya telah mencoba menggunakan rumus COUNTIF untuk sheet yang berbeda. Apakah rumus COUNTIF ini juga bisa diterapkan untuk file / Spreadsheet yang berbeda seperti di excel ?
Hi Firman,
For COUNTIF in Excel, please see this article.
Hi,
I have a set of data which contain client's name, month and status of the client which "active" or "not active". If I want to count the number of active clients for a particular month for example February. What formula should I use?
Any help would be appreciated. Thank you.
Hi Eirfan,
You'll find formula examples for your case in this part of the article:
Count in Google Sheets with multiple criteria — AND logic
Use the DATE function for the dates as conditions. Please see the example in this comment.
I have a table in which there are three stages of work, Done, working and Blocked which gets entered through a drop down,
Now, i want to show how many of the tasks are done in a x/y fashion where x being done and y being total no of tasks. I am getting the count of done task from countif but i want the output to be in given above fashion,
Hello Jay,
I believe this is what you're looking for:
=COUNTIF(A2:A10,"Done")&"/"&COUNTA(A2:A10)
where A2:A10 - your column with work stages
Learn more about these in the following articles:
COUNTA for Google Sheets
Concatenation operator: &
Hi! I would like to ask how to get the number of total states from different tab in Google sheet?
For example, first tab name PROGRAM PENDIDIKAN (PROGRAM) and second tab name BANTUAN PERSEKOLAHAN (PENDIDIKAN). So, in each tab, there is column for various state name.
So, i would like to know total count for each state added up from both tab [PROGRAM PENDIDIKAN (PROGRAM) & BANTUAN PERSEKOLAHAN (PENDIDIKAN)]
My formula: =COUNTIF('PROGRAM PENDIDIKAN (PROGRAM)'!F2:F72,"JOHOR" + COUNTIF('BANTUAN PERSEKOLAHAN (PENDIDIKAN)'!F2:F31,"JOHOR"))
It gives me 0 result.
Hi jenira,
Looks like you closed the first COUNTIF at the very end of the entire formula rather than right before the plus sign. Does it work if you move that closing bracket?
=COUNTIF('PROGRAM PENDIDIKAN (PROGRAM)'!F2:F72,"JOHOR") + COUNTIF('BANTUAN PERSEKOLAHAN (PENDIDIKAN)'!F2:F31,"JOHOR")
Attempting to determine if a there is an entry in the column being checked, THEN to find various counts in a set.
=if($b3="","",=countif($b$3:$b$36,=$e$2))
=if($b3="","",=countif($b$3:$b$36,=$f$2))___the two above appeared to return errors (the countif data were displayed in red)_____
=if($b3="","",=countif($b$3:$b$36,=$g$2))
=if($b3="","",=countif($b$3:$b$36,=$h$2)) These four did not return errors (the countif data were displayed in blue)
=if($b3="","",=countif($b$3:$b$36,=$i$2))
=if($b3="","",=countif($b$3:$b$36,=$j$2))
Attempted using COUNTIFS, and the same two were returning errors (indicated in red), while the last four were displayed in blue.
What am I missing?
Hello Guy,
Try to remove the equal signs before each COUNTIF and each $ at the end of the formulas. This kind of syntax is incorrect. The equal sign is required at the very beginning of the formula only.
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) (make sure it contains your updated formulas). I'll look into it and see if I can 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,
How do I count if C2 is x or y, D3 is x or y and E3 is x or y)
My example being:
I have Week 1, 2, 3 and I want to count if week 1, 2 and 3 is X or Y
So if week 1 was x, week 2 was y and week 3 was x again, I want that to return 1
but equally is week 1 was y, week 2 was y and week 3 was x.
Hope that makes sense. Love to know how to do it.
Hi Jack,
Looks like you need IF or even IFS function for the job. SWITCH may come in handy as well.
Hello,
I have created a sheet that automatically records date and time by typing their name into column B.
I then need to count how many times each month we add a value.
I have used this formula in order to do so. =COUNTIFS($F$3:$F$102, $P3, $F$3:$F$102, $Q3)
Where Column F = the date
Where P3=>=01/01/2023 and Q3=<=31/01/2023
This has worked, however when I now type into Column B and a date and time is recorded I have to re write the data in order for it to count. Every consequent input adds to the count within the range. It is only when the first value is input that the data does not record it.
Can anyone help me to understand why?
Hello Matt,
Sorry, it's hard to tell without seeing the data.
For me to be able to help you, please share an editable copy of your spreadsheet with us (support@apps4gs.com) (make sure it contains your formula).
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 and see if I can help.
Hi!
I'm trying to come up with a Google sheet formula that counts the number of times a topic was discussed in a month but on different days because the topics are discussed multiple times in a day. Here's the sample file: https://docs.google.com/spreadsheets/d/1jZjihrrYL1E1aTr2g-cxEUCQnt8a8uOH-FtLBGh8stU/edit#gid=0
Hi JR,
For October, use the following formula:
=ARRAYFORMULA(SUM(IF(($B$2:$B$11=B2)*(MONTH($C$2:$C$11)=10), 1/COUNTIFS($B$2:$B$11, B2, $C$2:$C$11, $C$2:$C$11, MONTH($C$2:$C$11), 10), 0)))
For November, replace 10 for MONTH with 11.
I could really use some help. I am trying to figure out how to use a count function based on several criteria.
For example, count only if:
3D = 2 and 3N = 1, but ONLY IF 2D = 1 and 2N = 1.
Does this make sense? Is something like this even possible?
Thanks for any help!
Hi! Use the COUNTIFS function to calculate values over multiple conditions. I cannot recommend the formula to you, as I do not understand what 3D=2 means.
Hi there!
I'm so grateful for you for writing these articles! I'm stuck on 1 thing. I'm trying to create a formula to repeat a fixed value every 3 months for X times.
I think I have the formula to repeat the fixed value every 3 months, but I'm not sure on how to "end" the calculation with how many times it needs to run. Ie. I receive a payment of $200 every 3 months, which will happen 5 times.
Here's the formula I'm working with right now; =IF(F18=start,(MOD(DATEDIF(start,F18,"m")+D19,D19)=0)*200,0)
F18 is the date on the calendar (going by months)
Start is the day payments started
D19 is the cell that indicates every 3 months
And 200 is the value
But how do I close this string off after so many times?
Thank you! Have an amazing day!
Hi! If I understand your task correctly, to create a sequence of dates with an interval of 3 months from the start date, try this formula:
=ARRAYFORMULA( date(YEAR($F$18),MONTH($F$18)+SEQUENCE(5,1,0,3),day($F$18)))
You can learn more about SEQUENCE function in this article on our blog.
I have a mixed list of middle and high schoolers and I want to be able to provide an at-a-glance number of how many of each are in my table.
Essentially, I'd like a formula that counts the number of times "9th" "10th" "11th" and "12th" appear in the column while ignoring the "6th" "7th" and "8th" values, and vice versa, so that I can have separate outputs for the number of middle schoolers and the number of high schoolers in my dataset.
Thanks!
Hello Chris,
COUNTIF described above is perfect to count pupils from each class.
If you'd like to count the total from 4 grades, you'll need to sum a few COUNTIF formulas, like in the example here.