ExcelBanter - View Single Post - Grouping and summing values based on a condition?

Dallman Ross

In .com,
spake thusly:

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=