SOLVED

# Sum of Total Paid for each ID

Copper Contributor

# Sum of Total Paid for each ID

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.

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
3 Replies

# Re: Sum of Total Paid for each ID

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.

# Re: Sum of Total Paid for each ID

Hi @mathetes Thank you for sharing I'm using Excel version 2016. Is it possible to modify? Please let me know thank you
best response confirmed by smanas87 (Copper Contributor)
Solution

# Re: Sum of Total Paid for each ID

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.