Laku
The COUNTIF function is useful to count certain data with conditions over a range of cells in Google Sheets. But very often we don’t have enough space in a single sheet to apply the COUNTIF function due to a large amount of data. In such cases, applying COUNTIF from another workbook in Google Sheets can make things clear without creating any hodgepodge.
A Sample of Practice Spreadsheet
You can download the spreadsheets from the link below. The spreadsheets contain a dataset we use here. After downloading you can practice on your own as we demonstrate here.
Introduction to COUNTIF Function in Google Sheets
Objective
The COUNTIF function counts the number of cells over the ranges based on the given condition.
Syntax
COUNTIF(range, criterion) 
Inputs
- range: The range over which the COUNTIF function counts.
- criterion: The criterion based on which the COUNTIF function counts the number of cells.
Output
Returns the number of cells that meet the condition.
2 Easy Examples of Using COUNTIF Function to Count Cells from Another Workbook in Google Sheets
We use the following dataset to demonstrate how to use COUNTIF from another workbook in Google Sheets with two easy examples. We can use COUNTIF from single as well as multiple workbooks.
1. COUNTIF from Single Workbook
In this example, we want to explore COUNTIF from a single workbook.
1.1 Contain Given Text from Another Workbook
We can use the COUNTIF function to count the number of cells that contain specific text. For example, we can count the number of times Cereal occurs in the Item Type column.
📌 Steps:
- Firstly, we open a new sheet and select a cell in which we want to apply the COUNTIF from another workbook. Here, we open a new sheet and select cell C4 to apply the COUNTIF function.
=COUNTIF('COUNTIF Workbook1'!B5:B13,'COUNTIF Workbook1'!B7)- Then we write down the above formula in the formula bar.
This gives us the number of times cell B7 of COUNTIF Workbook1 or Cereal occurs within the range of B5:B13 from COUNTIF Workbook1.
Read More: Use COUNTIF If Cell Contains Specific Text in Google Sheets
1.2 Based on Logical Expression
COUNTIF offers us the opportunity to count based on logical expression when working with numerical data.
📌 Steps:
- First, we open a new sheet and select a cell in which we want to apply the COUNTIF function as earlier. Here, we open a new sheet and select cell C4 to apply COUNTIF from another workbook.
=COUNTIF('COUNTIF Workbook1'!E5:E13,">300")- Then we put the above formula in the formula bar.
This gives us the number of times Unit Cost is greater than 300 within the range of E5:E13 from COUNTIF Workbook1.
Read More: How to Use COUNTIF Function with OR Logic in Google Sheets
1.3 Cells that are Blank
The COUNTIF function can also be used to count the Blank or Non-Blank cells within the given range.
📌 Steps:
- As earlier, we open a new sheet and select a cell in which we want to apply the COUNTIF Here, we open a new sheet and select cell C4 to apply COUNTIF function.
=COUNTIF('COUNTIF Workbook1'!D5:D13,"")- Then we write down the above formula in the formula bar.
This gives us the number of blank cells within the range of D5:D13 from COUNTIF Workbook1. In our case, there is no blank cell within the given range.
Read More: COUNTIF Function with “Not Equal to” Criterion in Google Sheets
1.4 Cells Based on Date
We can count the number of occurrences based on a given date using the COUNTIF function as well.
📌 Steps:
- Firstly, we open a new sheet and select a cell in which we want to apply the COUNTIF from another workbook. Here, we open a new sheet and select cell C4 to apply COUNTIF function.
=COUNTIF('COUNTIF Workbook1'!D5:D13,"1/1/23")- Then we put the above formula in the formula bar.
This gives us the number of orders that are placed on the given date within the range of D5:D13 from COUNTIF Workbook1.
1.5 Use of Wildcard for Partial Match
We can use wildcard characters as a part of the text in COUNTIF function. For example, here we use “Office*” to count the number of times Office Appliances appears in the Item Type column.
📌 Steps:
- First of all, we open a new sheet and select a cell in which we want to apply the COUNTIF Here, we open a new sheet and select cell C4 to apply COUNTIF function.
=COUNTIF('COUNTIF Workbook1'!B5:B13,"Office*")- Then we write down the above formula in the formula bar.
This gives us the number of times Office Appliances appears within the range of B5:B13 from COUNTIF Workbook1.
Read More: How to Use COUNTIF for Cells Not Equal to Text in Google Sheets
2. COUNTIF Across Multiple Workbooks
In this example, we want to use COUNTIF across multiple workbooks. So, we add another workbook with the previous workbook. An image of the second workbook is added below for convenience.
📌 Steps:
- Initially, we open a new sheet and select a cell in which we want to apply the COUNTIF Here, we open a new sheet and select cell C4 to apply COUNTIF function.
=COUNTIF('COUNTIF Workbook1'!C5:C13,"Retail")+COUNTIF('COUNTIF Workbook2'!C5:C13,"Retail")- Then we write the above formula in the formula bar.
This gives us the number of Retail orders within the range of C5:C13 from both COUNTIF Workbook1 and COUNTIF Workbook2.
Read More: [Fixed!] COUNTIF Function Is Not Working in Google Sheets
How to Use COUNTIFS Across Multiple Workbooks in Google Sheets
The COUNTIF function won’t be much use if want to count cells based on multiple criteria from multiple sheets. Here comes the COUNTIFS function. It can count cells based on multiple criteria as well as from multiple sheets.
📌 Steps:
- As usual, we open a new sheet and select a cell in which we want to apply the COUNTIF Here, we open a new sheet and select cell C4 to apply COUNTIF function.
=COUNTIFS('COUNTIF Workbook1'!E5:E13,"<100",'COUNTIF Workbook2'!E5:E13,"<100")- Then we write down the above formula in the formula bar.
This gives us the minimum number of orders less than 100 that appear both in COUNTIF Workbook1 and COUNTIF Workbook2 within the range of E5:E13.
Read More: COUNTIF with Greater than and Less than Criteria in Google Sheets
Things to Remember
- Be careful about the range of the COUNTIF function.
- Try to use COUNTIFS, if there are multiple conditions.
Conclusion
In this article, we try to cover various aspects of COUNTIF from another workbook in Google Sheets. I hope from now on you can easily decide where to use COUNTIF from another workbook and how to use it. Further, If you have any questions regarding this article feel free to comment below and I will try to reach out to you soon. Visit our website OfficeWheel for most useful articles.
