Excel Formulas to Count Variable Range with COUNTIFS Function!!
Tags: excelformula, excelformulas
Published: 2021-07-12 12:18:40

---
title: Excel Formulas to Count Variable Range with COUNTIFS Function!!
published: true
date: 2021-07-09 05:33:29 UTC
tags: ExcelFormula,Excelformulas
canonical_url: https://geekexcel.com/excel-formulas-to-count-variable-range-with-countifs-function/
---

We know that the **COUNTIFS function** in Excel can count on multiple criteria. It takes arguments as a couple of criteria range and criteria. Here I will introduce you to some quick tricks to count the variable range using the COUNTIFS function in Excel. Let’s get started!! Get an official version of ** MS Excel**  from the following link: [https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)

[![Count variable range with COUNTIFS](https://geekexcel.com/wp-content/uploads/2021/07/Count-variable-range-with-COUNTIFS.png)](https://geekexcel.com/excel-formulas-to-count-variable-range-with-countifs-function/count-variable-range-with-countifs/#main)<figcaption id="caption-attachment-48722">Count variable range with COUNTIFS</figcaption>

## Generic Formula:

- If you want to configure COUNTIFS (or COUNTIF) with a variable range, you can use the below formula.

**=COUNTIFS([OFFSET](https://geekexcel.com/lookup-and-reference-functions-in-microsoft-excel-365/)(A$1,0,0,[ROW](https://geekexcel.com/row-function-in-microsoft-excel-365-how-to-use/)()-ROW(A$1)-1,1),”<>”)**

## Syntax Explanations:

- **COUNTIFS** – This function helps to create a cell address from a given row and column.
- **OFFSET** – In Excel, the [**OFFSET function**](https://geekexcel.com/lookup-and-reference-functions-in-microsoft-excel-365/) will return the smallest numeric value from the range of input values.
- **ROW** – The **[ROW function](https://geekexcel.com/row-function-in-microsoft-excel-365-how-to-use/)** helps to return the row number for reference.
- **A1** – It represents the input value.
- **Comma symbol (,)** – It is a separator that helps to separate a list of values.
- **Parenthesis ()** – The main purpose of this symbol is to group the elements.

## Practical Example:

Refer to the below example image.

- First, we will enter the input values in **Column B**  to **Column E**.
- Here we are going to count the active cells in a row.

![Input Ranges](https://geekexcel.com/wp-content/uploads/2021/07/Input-Ranges-32.png)<figcaption id="caption-attachment-48724">Input Ranges</figcaption>

- Select any cell and type the above-given formula.

![Enter the formula](https://geekexcel.com/wp-content/uploads/2021/07/Enter-the-formula-31.png)<figcaption id="caption-attachment-48723">Enter the formula</figcaption>

- Finally, press the ** ENTER**  key, you can get the result as shown below.

![Result](https://geekexcel.com/wp-content/uploads/2021/07/Result-32.png)<figcaption id="caption-attachment-48725">Result</figcaption>

## Conclusion:

In this blog post, we have described the useful formulas to **count the variable range with COUNTIFS in Excel**. Please share your **query** below in the **comment** box. We will assist you.

Thank you so much for visiting  **[Geek Excel](https://geekexcel.com/)!! **If you want to learn more helpful formulas, check out [**Excel Formulas**](https://geekexcel.com/excel-formula/) **!! **

### Read Also:

- **[Excel Formulas to Test All Values in a Range are at least Certain Threshold](https://geekexcel.com/excel-formulas-test-all-values-in-a-range-are-at-least-certain-value/)**
- **[How to Insert a Date or Formatted Date in Excel Office 365?](https://geekexcel.com/how-to-insert-a-date-or-formatted-date-in-excel-office-365/)**
- **[Excel Formulas to Find the Last Cell Address in a Range ~ Quick Tricks!!](https://geekexcel.com/excel-formulas-find-last-cell-address/)**
- **[Excel Formulas to Generate Random Date between Two Dates!!](https://geekexcel.com/excel-formulas-to-generate-random-date-between-two-dates/)**