I have a spreadsheet with about 20 columns in it. I want to take the dollar
amounts of column T that have the same batch number in Column R and make a
sum of the numbers in Column T that have the same batch number in Column R.
Does anyone have any idea how I can do this?

Try this

=SUMPRODUCT((R1:R20=Q1)*(T1:T20))

Where Q1 is the batch number you want the sum of.

Mike

"Seth" wrote:

I have a spreadsheet with about 20 columns in it. I want to take the dollar
amounts of column T that have the same batch number in Column R and make a
sum of the numbers in Column T that have the same batch number in Column R.
Does anyone have any idea how I can do this?

Mike,
That didn't work the batch numbers in column R are different. There maybe
any where from 1 to 20 amounts that have the same batch number on about 500
rows. Is there a way of doing the formula in a new column to show the total
for each batch number?

"Mike H" wrote:

Try this

=SUMPRODUCT((R1:R20=Q1)*(T1:T20))

Where Q1 is the batch number you want the sum of.

Mike

"Seth" wrote:

I have a spreadsheet with about 20 columns in it. I want to take the dollar
amounts of column T that have the same batch number in Column R and make a
sum of the numbers in Column T that have the same batch number in Column R.
Does anyone have any idea how I can do this?

Create a list of each unique batch number, you can do that by making sure
you have a header in column R and then select header and all the batch
numbers, do datafilteradvanced filter, select unique records only and then
copy to another location, assume you copy it to U1 that will put the header
in U1 and all the unique entries below. Then in cell V2 adjacent to U2 put

=SUMPRODUCT(--($R$2:$R$200=U2),$T$2:$T$200)

that will give you the totals for what's in U2, now copy down the formula
as long as needed.
It will change to



=SUMPRODUCT(--($R$2:$R$200=U3),$T$2:$T$200)


=SUMPRODUCT(--($R$2:$R$200=U4),$T$2:$T$200)


and so on



Or you can copy column R and Column T to another sheet
next to each other, put headers above them unless they already have that.
Select both columns and sort by the batch numbers so you group them
then apply datasubtotals, it will probably default to sum former T for each
change in former
R.



--


Regards,


Peo Sjoblom

"Seth" wrote in message
...
Mike,
That didn't work the batch numbers in column R are different. There maybe
any where from 1 to 20 amounts that have the same batch number on about
500
rows. Is there a way of doing the formula in a new column to show the
total
for each batch number?

"Mike H" wrote:

Try this

=SUMPRODUCT((R1:R20=Q1)*(T1:T20))

Where Q1 is the batch number you want the sum of.

Mike

"Seth" wrote:

I have a spreadsheet with about 20 columns in it. I want to take the
dollar
amounts of column T that have the same batch number in Column R and
make a
sum of the numbers in Column T that have the same batch number in
Column R.
Does anyone have any idea how I can do this?