Menu

Grouping and summing values based on a condition?

Search this Thread

#1

Report Post

Posted to microsoft.public.excel.programming

Dallman Ross Dallman Ross is offline

Dallman Ross is offline

external usenet poster

Posts: 390

Default

Grouping and summing values based on a condition?

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=

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Summing Data based upon a condition	Chris	Excel Worksheet Functions	2	April 8th 10 06:19 PM
Summing rows based upon condition	Tom	Excel Discussion (Misc queries)	2	February 7th 10 06:39 PM
excel summing N largest values by condition	[email protected]	Excel Discussion (Misc queries)	5	November 23rd 07 02:08 PM
summing up values in a column based on values in 3 other columns	Axel	Excel Programming	4	March 30th 07 10:40 PM
Summing an array based on text condition	RestlessAde	Excel Discussion (Misc queries)	3	April 21st 05 05:44 PM

All times are GMT +1. The time now is 12:58 PM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement