Sum of Total Paid for each ID

[smanas87]

Hello I'm trying to calculate Total paid amount 6m prior and 6m after initial based on the Paid, 6m prior to initial and 6m post initial columns. 

Please see below. Please advise a formula Thank you

Expected output is for ID 1 since 6m prior to initial have 1's on all 3 cells (6m prior to Initia) total paid amount 6m prior to intial =$695

ID 2 will have total paid amount 6m post initial = $110 since the 6m post initial cells are 1's

ID	Yearmonth	Paid	6m prior to Initial	6m after initial	total paid amount 6m prior to inital	total paid amount 6m post initial
1	202205	375	1	0
1	202205	320	1	0
2	202101	50	0	1
2	202106	60	0	1

[#2]

@smanas87 

I would put these sums elsewhere on the spreadsheet, since you are basically (in the case of your examples at any rate), displaying two rows on the input end of things but expecting one row at the output.

This is the formula I used (which does require Excel 2021 or newer).

=SUM(FILTER(Table1[Paid],(Table1[ID]=J2)*(Table1[6m prior to Initial]=1),0))

Below is an image of the sheet, and I've attached the actual spreadsheet.

[#3]

Hi @mathetes Thank you for sharing I'm using Excel version 2016. Is it possible to modify? Please let me know thank you

[#4 · Accepted solution]

@smanas87 

Done. See attached. The SUMIF function now does the totaling by ID.

I do recommend upgrading, however. There are quite a few new functions and capabilities in Excel, capabilities that are very exciting.