Calculating clients owed amounts month by month

[paloniemi159]

Hello,
I have data of client owed amounts in one month, and in the next month the amount changes (increases or a new client with a new amount appears, decreases or an old client pays it off and is not in the next months data, or the amount remains the same)
I would like to calculate the change between months and need 2 outputs: new client amounts + increased amounts (by how much an amount increased to be exact); old client amounts that are gone by next month + decreased amounts (like previously, by how much an amount decreased).
Is there a way to do it with a pivot table or just using formulas is easier?
I have attached an example with some more explanation and an end result which the formula should calculate.
Thank you!

ExcelAmounts.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2		ClientID	amount	date
3		Bob	5	2022-11-30			for example, Bob had a total of			16	amount in November
4		Bob	10	2022-11-30			in December Bob had a total of			6
5		Bob	0	2022-11-30			So it decreased by 10, I need to sum up all of these clients
6		Jake	20	2022-11-30			another example: Max had 11 in November
7		Alex	4	2022-11-30			In December this client is gone, so this number (11) goes to the "Decreased" pile together with Bobs 10 (total 10+11=21)
8		Phil	3	2022-11-30
9		Aron	0	2022-11-30			same goes for Increased amounts
10		Max	11	2022-11-30			Mike had no amount in November, but in December he has a total of 25, so an increase of 25.
11		Bob	1	2022-11-30			Alex had 4 in November, he has 30 in December, so an increas of 26 (total 25+26=51)
12		Bob	0	2022-12-31
13		Bob	6	2022-12-31
14		Bob	0	2022-12-31
15		Jake	15	2022-12-31			End result should look like this
16		Alex	30	2022-12-31			74	New client amounts + increased amounts (december)
17		Phil	0	2022-12-31			29	Client amounts that are gone + decreased amounts (december)
18		Aron	23	2022-12-31
19		Mike	5	2022-12-31
20		Mike	20	2022-12-31
Sheet1

Cell Formulas
Range	Formula
J3	J3	=C3+C4+C5+C11
J4	J4	=C12+C13+C14
G16	G16	=(C16-C7)+(C18-C9)+C19+C20
G17	G17	=(C3+C4+C5+C11-C12-C13-C14)+(C6-C15)+(C8-C17)+C10

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Calculating clients owed amounts month by month
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.