How to SUM UP A COLUMN that matches

[SDKBRK]

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.

 

[DonkeyOte]

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

 

[fairwinds]

Hi,

Maybe like this?

Book1
	A	B	C	D
1
2	507755	64
3		274	338
4	305555	5
5		1
6		1	7
7	304444	6	6
8	507755	35	35
Sheet4

 

[SDKBRK]

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

 

[Ahnold]

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.

 

[Aladin Akyurek]

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

Book3
	A	B	C	D
1	Item	Value	SubTotal	RowIdx
2	050-7755	-64	-343	2
3		-274
4	030-5555	-5	-13	4
5		-1
6		-1
7	030-4444	-6	-41	7
8	050-7755	-35	-35	8
9
Sheet1

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