Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula needed please... | New Users to Excel | |||
Formula Needed | Excel Discussion (Misc queries) | |||
Sum formula needed | Excel Discussion (Misc queries) | |||
Formula Needed | Excel Discussion (Misc queries) | |||
Formula needed | Excel Worksheet Functions |