Establishing a variable range based on data size

**Bjordion** · 10-05-2011, 04:25 PM

I am working with an Excel file that lists each tenant's ID# in column B. Below each tenant ID# is a list of charge amounts for that tenant in column C, one charge per row. The number of charges varies for each tenant from month to month, and the row location of each tenant's ID# also will vary from month to month.

Example: In January, tenant A ID# is in cell B4, charges are in C6 through C17. Tenant B ID# is in cell B19, charges are in C21 through C25. In February, tenant A ID# is in cell B7, charges are in C9 through C16. Tenant B ID# is in cell B18, charges are in C20 through C30.

This information is located in one tab (see attached). Ultimately I want to extract the sum of charges for each tenant and list them in another tab.

Any and all help will be greatly appreciated.

**tlafferty** · 10-05-2011, 04:41 PM

To efficiently manage a list, you need to have clear labels for each piece of data, and a clear header row. See attached.

**Bjordion** · 10-05-2011, 04:48 PM

Originally Posted by tlafferty

To efficiently manage a list, you need to have clear labels for each piece of data, and a clear header row. See attached.

How can I automatically assign the tenant ID# to each charge? This is an exported Excel file from third party software that I am working with, and the total length of the file is about 20,000 rows long. It would be a major chore to manually copy/paste ID#'s for each charge.

**ChemistB** · 10-06-2011, 12:50 PM

Here's what I think is the simplest way.
First fill in all your blanks with your tenant ID as follows
Select B:B, F5 (GoTo) > Special
Select blank cells. Hit enter to go to B4 selected
In the formula bar, type = then hold down CNTRL and hit the up arrow (should see =B3 in formula bar)
CNTRL + ENTER to fill in all the blanks

Then in H4
=SUMIF(B:B, H3, C:C)
Does that work for you?

**Bjordion** · 10-07-2011, 10:29 AM

Originally Posted by ChemistB

Here's what I think is the simplest way.
First fill in all your blanks with your tenant ID as follows
Select B:B, F5 (GoTo) > Special
Select blank cells. Hit enter to go to B4 selected
In the formula bar, type = then hold down CNTRL and hit the up arrow (should see =B3 in formula bar)
CNTRL + ENTER to fill in all the blanks

Then in H4
=SUMIF(B:B, H3, C:C)
Does that work for you?

That worked brilliantly. Thank you!