Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369

    Unanswered: Adding part of a column in Excel

    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.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    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?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    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
    Attached Files Attached Files

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    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.
    Attached Files Attached Files
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    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

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •