Menu
Excel 2003 Question:
I'd like to have one cell represent a range of cells. So that another formula can use it.
Right now, I have an equation to sum the hours worked on a specific project. It looks for a project name, and then beside it, I have a column with how long i've spent on it. This is the formula =SUMIF(D1:D10,"project1",C1:C10)
If you look at the first picture,
I want B10 to say the range of Duration (B23-B28)
I want C10 to say the range of Project (C23-C28)
Now look at the second picture, with the formula.
Instead of having the range manually, I just want it to look at B10, and C10 so I get the range once. This way, I only have to change the range in one spot instead of how ever many projects I have.
I'm sorry if this is a messy problem. I'm not sure how to ask it, but I will be quickly answering comments if people aren't clear on what I'm trying to accomplish.
389
In a spreadsheet, a cell range is defined by the reference of the upper left cell (minimum value) of the range and the reference of the lower right cell (maximum value) of the range. Eventually separate cells can be added to this selection, then the range is called an irregular cell range.
Range Property. To refer to a single cell, you have to refer to a single cell. Syntax is simple “Range(“Cell”)”.
Colon ( : ) - This is used to define a range of cells (e.g. "A1:D4"). A range of cells is described in terms of the top left cell and the bottom right cell references separated by a colon. Comma ( , ) - This is used to define non adjacent cells (e.g. "A1", "B2"). The comma is also known as the union operator.
Total Cells in a Range By multiplying the total number of rows with the total number of columns in the same range, this gives us the total number of cells. To illustrate this, see the example below. The range of cells A1:D5 covers 5 rows and 4 columns.
Making it a bit more clear. You can enter the range (Address) as a text in the cells, and then use INDIRECT to reference them to a range instead of just text.
So enter
"C23:C46" in cell B10 (without quotes)
"D23:D46" in C10 (without quotes)
and use INDIRECT in your formula for SUMIF
Here's a picture for more clarity.
126
Store in the cell the address as mere text.
When using that range, use INDIRECT(B10).
See Excel DSUM with multiple criteria for a somewhat more complex usage example.
4
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
