Results 1 to 6 of 6

How to SUM UP A COLUMN that matches

This is a discussion on How to SUM UP A COLUMN that matches within the Excel Questions forums, part of the Question Forums category; Don't know how to word this. Here is an example of what I'm trying to do. A1 -050-7755 B1 -64 ...

  1. #1
    Board Regular
    Join Date
    Feb 2003
    Location
    Brighton, TN
    Posts
    124

    Default How to SUM UP A COLUMN that matches

    Don't know how to word this. Here is an example of what I'm trying to do.

    A1-050-7755 B1-64
    A2-BLANK B2-274
    A3-030-5555 B3-5
    A4-BLANK B4-1
    A5-BLANK B5-1
    A6-030-4444 B3-6
    A7-050-7755 B1-35


    In column C I want it to sum up B1 & B2 but not B3 because its a different item. How do I write that formula that will sum up qty's in columnB and stop when it comes to a blank cell in colA.

  2. #2
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,113

    Default Re: How to SUM UP A COLUMN that matches

    not sure your example makes sense but for a start look at SUMIF in XL Help.

  3. #3
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,633

    Default Re: How to SUM UP A COLUMN that matches

    Hi,

    Maybe like this?

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    ****
    2
    50775564*
    *
    3
    *274338*
    4
    3055555*
    *
    5
    *1*
    *
    6
    *17*
    7
    30444466*
    8
    5077553535*
    Sheet4*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
    "Fair Winds and Following Seas"

  4. #4
    Board Regular
    Join Date
    Feb 2003
    Location
    Brighton, TN
    Posts
    124

    Default Re: How to SUM UP A COLUMN that matches

    The formula works but I'm trying to have the formula in the same row as the item in col A. I'm been trying to get it to work with no luck. Any help would be appreciated. thanks

  5. #5
    Board Regular Ahnold's Avatar
    Join Date
    Feb 2004
    Location
    Baltimore, MD
    Posts
    636

    Default Re: How to SUM UP A COLUMN that matches

    Do the blank cells in column A represent the value of the same non-blank cell above it?

    If not, ignore the rest.

    If so, insert a new row above row 1 and enter a name for each column in A1 and B1. Select cell A1 and press Ctrl + Shift + 8* (all at the same time) and the entire range will be selected. Don't use the 8 or * key in the number pad, use the one above the "u" key. Then press F5. Select Special and then select blanks. This will highlight all of the blank cell within your range of cells. Press the "=" key and then press the up arrow and then press Ctrl + Enter. This will fill in all of the blank cells with the value of the non-blank cell above it.

    Then go back to A1 and select Data-Subtotals in the menu bar. Select the column name you gave for column A in the first dropdown box, sum in the second and check the name for column A in the third box and uncheck the name for Column B in the third box and hit OK.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,342

    Default Re: How to SUM UP A COLUMN that matches

    Since we have here a data representation that requires complex processing...

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book3___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    ItemValueSubTotalRowIdx
    2
    050-7755-64-3432
    3
    *-274*
    *
    4
    030-5555-5-134
    5
    *-1*
    *
    6
    *-1*
    *
    7
    030-4444-6-417
    8
    050-7755-35-358
    9
    ****
    Sheet1*

    [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Formulas...

    C2:

    =IF(A2<>"",SUM(B2:INDEX(B:B,MIN(D3:INDEX(D:D,MATCH(9.99999999999999E+307,D:D))))),"")

    D2:

    =IF(A2<>"",ROW(A2),"")

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com