# 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. ## 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. ## 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. ## 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
 C2C3C4C5C6C7C8 =

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.

4. ## 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. ## 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. ## 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
 C2D2C3D3C4D4C5D5C6D6C7D7C8D8 =

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),"")

#### Posting Permissions

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