Grouping and summing values based on a condition?

[annysjunkmail]

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

 

[Guest]

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

 

[shaqil]

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

 

[Dallman Ross]

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

This works and you can change it to suit.

Credits for 1 =SUM(IF(A2:A10=1,IF(B2:B10="Credit",C2:C10)))

This is an array formula. You need to hold down the Control+Shift
keys while pressing Enter to enable it once you type it in.

You can alter it to get your other subtotals. However, if you
really only have two IDs, then you could save calculation effort in
Excel by using simpler (non-array) formulas for the other three.
For example, assuming only ID 1 or ID 2, this works for me:

Payments for 1 =SUMIF(A:A,1,C:C)-F5

("F5" is where I had the "Credits for 1" formula.)

Credits for 2 =SUMIF(B:B,"Credit",C:C)-F5

Payments for 2 76,388.52 =SUM(C:C)-F7-F6-F5
("F7", "F6", and "F5" are where I had the previous formulas
above.)

Well, you don't need all that. You can just use an array formula
modelled on the first formula up above -- the array formula --
and change the ID or the word "Credit"/"Debit" to suit.

There would be other approaches possible too, but this works
fine for me with your data.


=dman=