Sum of Total Paid for each ID

Copper Contributor


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


IDYearmonthPaid6m prior to Initial6m after initialtotal paid amount 6m prior to initaltotal paid amount 6m post initial


3 Replies



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.



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)



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.