This tutorial explores the powerful use of Countifs with Isbetween in Google Sheets. Not only will you learn how to use this function, but you’ll also discover other matching criteria that can be used in combination with it.
Imagine you want to keep track of how many times you filled fuel in your vehicle during a specific period. Countifs with Isbetween allows you to match the start and end dates (the given period) in a date column and “fuel” as the matching criteria in a category column. This combo is valuable because it helps avoid formula mistakes that often occur when using comparison operators.
In Countifs, comparison operators need to be entered in double quotation marks, which is often overlooked. However, Isbetween eliminates this requirement. Instead of using operators like >, >=, <, and <=, you can utilize the Isbetween function with Countif or Coutifs in Google Sheets.
Countifs with Isbetween is ideal for counting values that fall within two dates or numbers and one or more corresponding/matching criteria.
Countifs with Isbetween in Google Sheets: How to
The syntax of the Countifs function is as follows: COUNTIFS(criteria_range1, criterion1, [criteria_range2, …], [criterion2, …]). However, when using Isbetween, the structure of the formula changes.
You use comparison operators within the criterion part when using Countifs, while Isbetween is used with the criteria_range part. The criterion in the Isbetween case should be changed to Boolean TRUE.
Let’s take a look at two examples to understand this better.
Example 1: Countifs Between Two Dates
Suppose you have a dataset where column A displays client names and column B displays the PO (purchase order) dates. How do you use the Countifs function to find the number of POs between a start date and an end date?
The formula would look like this: =COUNTIFS(ISBETWEEN(B2:B, D2, E2), TRUE)
In this formula, criteria_range1 is ISBETWEEN(B2:B, D2, E2), and criterion1 is TRUE.
The Countifs and Isbetween combo is powerful, but you might wonder about the role of the Isbetween function. Take a look at the screenshot below to understand it better.
The Isbetween function tests whether the PO Date (value_to_compare) falls within the start date (lower_value) and end date (upper_value) and returns TRUE or FALSE. The Countifs function then counts the number of TRUE values.
In the formula I provided, both lower_value and upper_value are inclusive. If you want to exclude them, you can specify FALSE.
In the next example, we will see how to count POs between two dates and with one more matching criterion.
Example 2: Countifs Between Two Dates and Matching Criteria
In this example, the data range remains the same, with the client and PO dates in columns A and B. The criteria range includes the start date in D2, the end date in E2, and the client name “C” in F2. We want to find the number of POs received from client “C” between 05/05/2022 and 20/05/2022.
The formula would be: =COUNTIFS(ISBETWEEN(B2:B, D2, E2), TRUE, A2:A, "C")
If you want to count the purchase orders received between 05/05/2022 and 20/05/2022 from clients “A” and “D,” you can modify the formula as follows:
=ARRAYFORMULA(SUM(COUNTIFS(ISBETWEEN(B2:B, D2, E2, FALSE, FALSE), TRUE, A2:A, {F2,F3})))
By placing the text criterion references as an array using Curly Brackets and utilizing the Sum and ArrayFormula functions, you can achieve the desired result.
That’s all about Countifs between two dates and matching multiple criteria in Google Sheets.
Countifs with Isbetween and Multiple Start and End Dates
When you want to test multiple sets of start and end dates in Countifs with Isbetween, you can follow two methods: a drag-down formula or a Lambda formula.
For the drag-down formula, you need to replace B2:B with $B$2:$B and A2:A with $A$2:$A before dragging down. This is a simple and effective way to achieve the desired result.
Alternatively, you can use the Map Lambda function to create an array formula. This approach allows you to easily “spill” the Countifs formula down.
Here’s an example of how to use the Map function:
=MAP(D2:D4, E2:E4, LAMBDA(start, end, COUNTIFS(ISBETWEEN(B2:B, start, end), TRUE)))
To count POs between two dates and with matching criteria while spilling down, fill F2:F4 with the client name “A” and use the following formula:
=MAP(D2:D4, E2:E4, F2:F4, LAMBDA(start, end, client, COUNTIFS(ISBETWEEN(B2:B, start, end), TRUE, A2:A, client)))
Note: Avoid any blank cells in the criteria range.
Conclusion
Countifs with Isbetween is a powerful combination that allows you to count values that fall within specific date ranges and meet multiple matching criteria. By following the examples provided, you can easily apply this formula in your own worksheets.
If you encounter any issues, I recommend using the formulas in the same data ranges as shown in the examples. You can then adjust the ranges by inserting columns or rows as needed, and the formulas will adjust accordingly.
Feel free to explore related resources for more insights and solutions:
- COUNTIFS in a Time Range in Google Sheets [Date and Time Column]
- COUNTIF to Count by Month in a Date Range in Google Sheets
- Count Unique Dates in a Date Range – 5 Formula Options in Google Sheets
- Count Values Between Two Dates in Google Sheets
If you have any other questions or issues, please feel free to post in the comments. I’ll be happy to assist you.
