Trying to calculate payment amount for month when split over 4 payments different fields

[pitcrew]

so I have a spreadsheet that has 8 columns. In the first column is a date of 1st payment, the next column is amount of 1st payment, the third column is date of 2nd payment, four column is amount of 2nd payment, The fifth column is date of 3rd payment, six column is amount of 3rd payment, seven column is date of 4th payment, and last column is amount of 4th payment. Each row is a different customer, so each column will have multipole dates or amounts. I want to be able to see how much was paid each month total. Having a hard time getting this to work. I use the first formula below for the first two columns and it worked. I then tried to add on the next two column, and it broke right away. Use should now I am using Excel 2010 currently. any help you could provide you be greatly appreciated.


In the formula below A2 is "12/1/23" and B2 is "12/31/23" on a sperate sheet.


=IF(AND('Coaching'!I3:I5146>='Date 1'!A2,'Coaching'!I3:I5146<='Date 1'!B2),'Coaching'!J3:J5146,"0")

=IF(AND('Coaching'!I3:I5146>='Date 1'!A2,'Coaching'!I3:I5146<='Date 1'!B2),'Coaching'!J3:J5146,"0")+IF(AND('Coaching'!K3:K5146>='Date 1'!A2,'Coaching'!K3:K5146<='Date 1'!B2),'Coaching'!L3:L5146,"0")

 

[StephenCrump]

If your first formula worked, you were just lucky. The AND operator returns a single value, which implies that every date in I3:I5146 was in the required range.

For the 8 columns, try: =SUM((Coaching!I3:O5146>='Date 1'!A2)*(Coaching!I3:O5146<='Date 1'!B2)*ISEVEN(COLUMN(I3:O3)-COLUMN(I3))*Coaching!J3:P5146)

(Array-entered if using Excel 2010)

 

[pitcrew]

If your first formula worked, you were just lucky. The AND operator returns a single value, which implies that every date in I3:I5146 was in the required range.

For the 8 columns, try: =SUM((Coaching!I3:O5146>='Date 1'!A2)*(Coaching!I3:O5146<='Date 1'!B2)*ISEVEN(COLUMN(I3:O3)-COLUMN(I3))*Coaching!J3:P5146)

(Array-entered if using Excel 2010)

That does not work because it is looking at the amount columns that are in that range and returns an error of #VALUE! because the format's are different.

 

[pitcrew]

That does not work because it is looking at the amount columns that are in that range and returns an error of #VALUE! because the format's are different.

Diregard previous message this worked I forgot to make a it an Array. Thanks you so much.

 

[StephenCrump]

That's great. I'm glad we could help.