Is there a way in Excel that I can add part of a column, based on the cell entries in another column.

I know how to Sum a range of cells but I don't know how to handle the situation with rows being added or deleted or an entry in a reference cell being changed.

My ideal solution would be if I could have the equivalent of unbound text boxes at the bottom of an Access tabular form whereby adding or deleting records or different sorts are taken care of.

Howdy. Yes, something like this can be done. But can you post a sample workbook, including the expected result?

Also, you give two different criteria: 1) based on cell entries in another column (SUMIF if only one column, SUMPRODUCT with two or more columns), 2) "rows added or deleted". Can you clarify?

Attached Zip

As you will see Column P is "rent" and Column I is "view"

So what is required is the "rent" for different "views" So what is the rent being paid for Pool, City or whatever

I have enquired on behalf of a mate of mine. He wants to use Excel (and for very good reasons) but I don't have the knowledge in Excel.

Both of us did a bit with sorting and so Pool Views etc became cell range "whatever" but a resort of course gives the wrong answer. The spreadsheet gets replaced each month and during the month there are both additional rows added and rows deleted.

Many thanks for your offer of help.

Mike

On the attachment I used an extra worksheet (Sheet4), and listed all the unique values in column B, taken from column I on Sheet1. Then in column C on Sheet4 I put the SUMIF formula and copied down:

=SUMIF(Sheet1!$I:$I,$B2,Sheet1!$P:$P)

Is that what you were looking for? If not, let me know.

Many thanks. That sure did work.

=SUMIF(Sheet1!$I:$I,$B2,Sheet1!$P:$P)

What does the ! and $ tell Excel. I think I remember seeing the $ with VLOOKUP, which appeared to be similar to DLookup in Access

The exclamation point indicates that the reference prior to it is to a different worksheet (i.e. Sheet1) and is needed since the formula itself is on Sheet4.

The $ sign makes the reference absolute. The reference doesn't change columns if you copy it. If you want the columns to change, then take away the $ sign.

Thus:

$A$1 is an absolute reference to cell A1.

A$1 is absolute for the cell in row 1, but the column can change as it is copied

$A1, keeps the column reference absolute but the row number can change

A1 is relative, and will change column and row number.

===========

VLOOKUP is helpful, but even more flexibility/power comes by using INDEX/MATCH, which is high powered combination of VLOOKUP and HLOOKUP in one formula.