trying to write a formula that adds values in one column if another column
contains like values (cusip)

can't do sumif because the value changes every 2 - 10 cells or so and there
are 1400 lines in spreadsheet and about 300 cusips

I want it sum by cusip. I know there's a simple way but I've exhausted my
search.

example

cusip amt sum by cusip
912810FE3 300
912810FE3 500 800
912803BL6 100
912803BL6 200
912803BL6 300 600

Use a Pivot Table
--
Gary''s Student - gsnu200814


"novice" wrote:

trying to write a formula that adds values in one column if another column
contains like values (cusip)

can't do sumif because the value changes every 2 - 10 cells or so and there
are 1400 lines in spreadsheet and about 300 cusips

I want it sum by cusip. I know there's a simple way but I've exhausted my
search.

example

cusip amt sum by cusip
912810FE3 300
912810FE3 500 800
912803BL6 100
912803BL6 200
912803BL6 300 600

Assuming you have CUSIP in Col A and amount in Col B with header rows

IMP: Change 1500 in all formulas to the last row containing your data

1. Enter this in D2
=COUNTIF(A2:$A$1500,A2)
and copy down till end of your data

2. Filter your data on Col D equal to 1

3. Enter this in the first row (AND change 2 in A2 to the first row in the
filter)
=SUMPRODUCT(--($A$1:$A$1500=A2),$B$1:$B$1500)
Copy this formula to the filtered rows

4. Remove filter, you should have the formula above in rows with 1 in Col D

5. Delete Col D and you are done
"novice" wrote:

trying to write a formula that adds values in one column if another column
contains like values (cusip)

can't do sumif because the value changes every 2 - 10 cells or so and there
are 1400 lines in spreadsheet and about 300 cusips

I want it sum by cusip. I know there's a simple way but I've exhausted my
search.

example

cusip amt sum by cusip
912810FE3 300
912810FE3 500 800
912803BL6 100
912803BL6 200
912803BL6 300 600

try this
assumed Col A has cusip
Col B has amount
in Cell C2 put this formula and drag it down

=IF(A2=A3,"",SUMIF($A$2:$A$100,A2,$B$2:$B$100))



On Nov 17, 10:42*pm, novice wrote:
trying to write a formula that adds values in one column if another column
contains like values (cusip)

can't do sumif because the value changes every 2 - 10 cells or so and there
are 1400 lines in spreadsheet and about 300 cusips

I want it sum by cusip. *I know there's a simple way but I've exhausted my
search.

example

cusip * * * * * * *amt * sum by cusip
912810FE3 *300
912810FE3 *500 * *800
912803BL6 *100
912803BL6 *200
912803BL6 *300 * *600