Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grouping and summing values based on a condition?
I have 2 worksheets called TotalPyts & Customers
Sample data on wsheet TotalPyts looks like this: ID PytType Amt 1 Credit 68020.3 1 Payment 28381.68 1 Credit 1936.04 1 Payment 8753.39 1 Credit 16701.31 2 Payment 45277.56 2 Payment 24287.05 2 Payment 6823.91 2 Credit 26372.1 I am trying to calculate the sum of type of payments on wsheet Customers so it looks like the following: ID TotalCreditAmt TotalPaymentAmount 1 86657.65 37135.07 2 26372.1 76388.52 I have tried to use a SUMIF statement but it doesn't allow me to add the 'condition' as explained above. I do not want to do this by pivot table (my colleague find these difficult to interpret) so wish to use a worksheet function if possible. Grateful for help Chris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grouping and summing values based on a condition?
Hi,
Assuming your data are in columns A, B & C try:- =SUMPRODUCT((A$2:A$20=1)*(B$2:B$20="Credit")*(C$2: C$20)) =SUMPRODUCT((A$2:A$20=1)*(B$2:B$20="Payment")*(C$2 :C$20)) Would produce the sums for ID No1, modify for ID = 2 Mike " wrote: I have 2 worksheets called TotalPyts & Customers Sample data on wsheet TotalPyts looks like this: ID PytType Amt 1 Credit 68020.3 1 Payment 28381.68 1 Credit 1936.04 1 Payment 8753.39 1 Credit 16701.31 2 Payment 45277.56 2 Payment 24287.05 2 Payment 6823.91 2 Credit 26372.1 I am trying to calculate the sum of type of payments on wsheet Customers so it looks like the following: ID TotalCreditAmt TotalPaymentAmount 1 86657.65 37135.07 2 26372.1 76388.52 I have tried to use a SUMIF statement but it doesn't allow me to add the 'condition' as explained above. I do not want to do this by pivot table (my colleague find these difficult to interpret) so wish to use a worksheet function if possible. Grateful for help Chris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grouping and summing values based on a condition?
On Aug 7, 1:47 pm, wrote:
I have 2 worksheets called TotalPyts & Customers Sample data on wsheet TotalPyts looks like this: ID PytType Amt 1 Credit 68020.3 1 Payment 28381.68 1 Credit 1936.04 1 Payment 8753.39 1 Credit 16701.31 2 Payment 45277.56 2 Payment 24287.05 2 Payment 6823.91 2 Credit 26372.1 I am trying to calculate the sum of type of payments on wsheet Customers so it looks like the following: ID TotalCreditAmt TotalPaymentAmount 1 86657.65 37135.07 2 26372.1 76388.52 I have tried to use a SUMIF statement but it doesn't allow me to add the 'condition' as explained above. I do not want to do this by pivot table (my colleague find these difficult to interpret) so wish to use a worksheet function if possible. Grateful for help Chris Hi, u may use SUMIFS formula if ur using Office 2007 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grouping and summing values based on a condition?
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing Data based upon a condition | Excel Worksheet Functions | |||
Summing rows based upon condition | Excel Discussion (Misc queries) | |||
excel summing N largest values by condition | Excel Discussion (Misc queries) | |||
summing up values in a column based on values in 3 other columns | Excel Programming | |||
Summing an array based on text condition | Excel Discussion (Misc queries) |