Cumulative Sum with Blanks

[JJ93]

Hello,
I would like to do a cumulative sum of the products for each client. It should start from zero if a new client is mentioned and blanks should be treated as zero. I tried to do it with an Indirect(Adress) function,

new client, which did not work out. Here is the screenshot:

Any tips on how to do this?

Best regards,

JJJ

 

[maabadi]

Welcome to MrExcel Message Board.
Use Sumifs:
=Sumifs (SumRange, CriteriaRange,Criteria)
If Product is column D & clint is Column A Then
And 1 is Client Name at Column A

=Sumifs(D2:D100, A2:A100, 1)

Mail Generator2.xlsm
	A	B	C	D	E	F	G
1	Client	Date		Product		Client	Product Sum
2	1	11/13/2020				1	111
3	1	11/16/2020		74		2	123
4	1	11/19/2020
5	1	11/22/2020		25
6	1	11/25/2020
7	1	11/28/2020		12
8	1	12/1/2020
9	1	12/4/2020
10	2	12/7/2020
11	2	12/10/2020		56
12	2	12/13/2020
13	2	12/16/2020
14	2	12/19/2020		34
15	2	12/22/2020		22
16	2	12/25/2020		11
17	2	12/28/2020
18	2	12/31/2020
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=SUMIFS($D$2:$D$18, $A$2:$A$18,F2)

 

[JJ93]

Thanks for your reply. I think you understood me wrong. It should look like this. Unfortunately my main table is much larger, so I cant do it that easy. (Around 50000 products)

 

[maabadi]

Try this:

Mail Generator2.xlsm
	A	B	C	D	E
1	Client	Date		Product	Sum
2	1	11/13/2020			0
3	1	11/16/2020		74	74
4	1	11/19/2020			74
5	1	11/22/2020		25	99
6	1	11/25/2020			99
7	1	11/28/2020		12	111
8	1	12/1/2020			111
9	1	12/4/2020			111
10	2	12/7/2020			0
11	2	12/10/2020		56	56
12	2	12/13/2020			56
13	2	12/16/2020			56
14	2	12/19/2020		34	90
15	2	12/22/2020		22	112
16	2	12/25/2020		11	123
17	2	12/28/2020			123
18	2	12/31/2020			123
Sheet1

Cell Formulas
Range	Formula
E2:E18	E2	=SUMIFS($D$2:D2, $A$2:A2,A2)

 

[Olukunle]

=SUMPRODUCT((A$2:A2=A2)*C$2:C2)

 

[mohadin]

=SUMIFS($D$2:$D2, $A$2:$A2,A2)

 

[jasonb75]

If the original data is sorted by client ID as in the example then this would be far more efficient than sumif or sumproduct.

Unfortunately my main table is much larger, so I cant do it that easy.

If the output in @maabadi's first suggestion is preferable then it can be done far simpler than any formula by use of a basic pivot table.

Manpower plan.xlsx
	A	B	C	D	E
1	Client	Date		Product	Sum
2	1	11/13/2020			0
3	1	11/16/2020		74	74
4	1	11/19/2020			74
5	1	11/22/2020		25	99
6	1	11/25/2020			99
7	1	11/28/2020		12	111
8	1	12/01/2020			111
9	1	12/04/2020			111
10	2	12/07/2020			0
11	2	12/10/2020		56	56
12	2	12/13/2020			56
13	2	12/16/2020			56
14	2	12/19/2020		34	90
15	2	12/22/2020		22	112
16	2	12/25/2020		11	123
17	2	12/28/2020			123
18	2	12/31/2020			123
Sheet6

Cell Formulas
Range	Formula
E2:E18	E2	=SUM(D2,IF(A2=A1,E1))

 

[JJ93]

Works perfectly, thanks a lot!

 

[maabadi]

You're Welcome & Thanks for feedback.

 

[mohadin]

Thank You