Excel Formulas to Define Range Based on Cell Value ~ Quickly!!
Tags: excelformula, excelformulas
Published: 2021-07-13 03:21:43

---
title: Excel Formulas to Define Range Based on Cell Value ~ Quickly!!
published: true
date: 2021-07-09 06:30:52 UTC
tags: ExcelFormula,Excelformulas
canonical_url: https://geekexcel.com/excel-formulas-to-define-range-based-on-cell-value/
---

To calculate a range of values is easy for most Excel users, but have you ever tried to calculate a range of values based on the number in a specific cell? Here we will show the formulas to **define a**  **range based on another cell value in Excel**. Let’s dive into this article!! 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)

[![Define range based on another cell value](https://geekexcel.com/wp-content/uploads/2021/07/Define-range-based-on-another-cell-value.png)](https://geekexcel.com/excel-formulas-to-define-range-based-on-cell-value/define-range-based-on-another-cell-value/#main)<figcaption id="caption-attachment-48741">Define range based on another cell value</figcaption>

## General Formula:

- If you want to sum the values based on some criteria, you can use the below formula.

**=[SUM](https://geekexcel.com/sum-function/)(firstcell:[INDEX](https://geekexcel.com/index-function-in-microsoft-excel-365-how-to-use/)(data,rows,cols))**

## Syntax Explanations:

- **SUM** – The [**SUM function**](https://geekexcel.com/sum-function/) adds values to the cells.
- **INDEX** – In Excel, the [**INDEX function**](https://geekexcel.com/index-function-in-microsoft-excel-365-how-to-use/) will return the value at a given position in a range or array.
- **First cell** – This specifies the first cell of a given input range.
- **Data** – It represents the input range.
- **Rows** – It represents the row number.
- **Cols** – It specifies the column number.
- **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:

Let’s consider the below example image.

- Here we will enter the input values in **Column B** to **Column G**.
- Now we are going to sum the values based on the given row and column numbers in **Column J**.

![Input Ranges](https://geekexcel.com/wp-content/uploads/2021/07/Input-Ranges-33.png)<figcaption id="caption-attachment-48739">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-32.png)<figcaption id="caption-attachment-48738">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-33.png)<figcaption id="caption-attachment-48740">Result</figcaption>

## Closure:

Hope you like this article on how to **define a range based on another cell value in Excel**. If you have any **questions** or **suggestions** regarding this lesson, feel free to use the **comments** section below to let us know.

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 Count Variable Range with COUNTIFS Function!!](https://geekexcel.com/excel-formulas-to-count-variable-range-with-countifs-function/)**
- **[Formulas for Counting the Cells Greater than a Given Values in Excel!!](https://geekexcel.com/formulas-for-counting-the-cells-greater-than-a-given-values-in-excel/)**
- **[Excel Formulas to Find the Address of First Cell in a Range ~ Easily!!](https://geekexcel.com/excel-formulas-to-find-the-address-of-first-cell-in-a-range/)**
- **[Excel Formulas to Randomly Assign Data to Groups ~ Easy Tricks!!](https://geekexcel.com/excel-formulas-to-randomly-assign-data-to-groups/)**