Subtract value over multiple cells

Blake0920

Board Regular
Joined
Jan 2, 2022
Messages
60
Office Version
  1. 2016
Platform
  1. Windows
Trying to figure out a formula where I can subtract a value from a series of cells.

This is what I have now but it is not putting that value in any of the cells. =SUM(((Y12:Y18*(AD5:AD5="8")*(AA5:AA5>="02-01-2022")*(AA5:AA5<="02-31-2022"))))-AE5
 

Attachments

  • Screenshot 2022-02-21 225933.png
    Screenshot 2022-02-21 225933.png
    33.4 KB · Views: 24
Okay i just wanted to clarify any confusion as my question has been looked at dozens of times
Bumping you thread 4 times in about as many hours does not help your chances of getting a solution.
 
Upvote 0

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Hi,

I gave you a suggestion in your Duplicate post ( which is now closed ), I think the problem with lack of help here is your description being unclear.
The samples you gave Lacks Parts of your sheet, you have Formula referencing Columns/Cells that are Not shown (e.g. your Rents are coming from Column O, which we can not see), so helpers can only Guess what they might be.
That combined with unclear description is likely why you haven't gotten your question answered.
 
Upvote 0
This is my best Guess.
You have 2 Columns of text months, M & O, I'm using Column M, as you didn't specify the difference.
Your formula references the Dates in Column Q, but you didn't explain the significance ( and 2/31/2022 doen't exist )
My formula for Column P does Not account for the Year, as you did not explain or give guidelines regarding, so Will error past December.

Book3.xlsx
MNOPQRSTUV
4mo paymentName Cancel DateOG Mo PPOwing DurationMo. Total
52/15/202212863.338107.921
632/16/20226573.213191.071
76
89
912
10
11Current Owing Reduced
12feb31492.43mar298.9931193.44
13mar27025.93apr298.9926726.94
14apr24164.54may298.9923865.55
15may23292.13jun107.9223184.21
16jun20192.76jul107.9220084.84
17jul17857.81aug107.9217749.89
18aug15584.18sep107.9215476.26
19sep13762.83oct107.9213654.91
20oct11715.2nov011715.2
21nov9562.18dec09562.18
22dec5771.53jan05771.53
23jan1095feb1095
24febmar
Sheet1017
Cell Formulas
RangeFormula
P12:P22P12=SUMPRODUCT((MONTH(M12&"/1")+0<=T$5:T$6+1)*U$5:U$6)
R12:R23R12=N12-P12
 
Last edited:
Upvote 0
Thank you for this. You almost got it exact.

Column R(former M) has no significance.

The date column, Q, is when the monthly payment should start. For example, the customer stops paying in the month of Feb-22 - it should take the duration and multiply that by how many months. Which you have solved!

But for example, if the person stops paying in Mar-22 I would need the payment (U) to subtract for the duration starting in Mar-22. I tried to add the date column for Feb but I keep getting #spill.

I did not include this below, but this is the formula I am trying to work with. =SUMPRODUCT((MONTH(P14&"/1")+0<=T$5:T$6+1)*U$5:U$6)*(Q5:Q7>="02-01-2022")*(Q5:Q7<="02-31-2022")

Sales Orders 2022-2.1 - Blake.xlsm
PQRSTUV
4Name Cancel DateOG Mo PPOwing DurationMo. Total
502-15-202212$863.338$107.921
602-16-20226573.213$191.071
702-16-2022910234$255.751
8
9
10
11
12
13Current Owing Reduced
14feb$31,492.43mar$298.99$31,193.45
15mar$27,025.93apr$298.99$26,726.94
16apr$24,164.54may$298.99$23,865.56
17may$23,292.13jun$107.92$23,184.21
18jun$20,192.76jul$107.92$20,084.84
19jul$17,857.81aug$107.92$17,749.89
20aug$15,584.18sep$107.92$15,476.26
21sep$13,762.83oct$107.92$13,654.91
22oct$11,715.20nov $11,715.20
23nov$9,562.18dec $9,562.18
24dec$5,771.53jan $5,771.53
25jan$1,095.00feb$1,095.00
26feb mar 
PP Mo
Cell Formulas
RangeFormula
U5:U7U5=S5/T5
S14:S24S14=SUMPRODUCT((MONTH(P14&"/1")+0<=T$5:T$6+1)*U$5:U$6)
Q14Q14=(D5/J5)/M9*(J5)+(D8/J8)/M9*(J8)+(D11/J11)/M9*(J11)+(D14/J14)/M9*(J14)+(D17/J17)/M9*(J17)+(D20/J20)/M9*(J20)+(D23/J23)/M9*(J23)+(D26/J26)/M9*(J26)+(D29/J29)/M9*(J29)+(D32/J32)/M9*(J32)+(D35/J35)/M9*(J35)+(D38/J38)/M9*(J38)
Q15Q15=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D8/12)+(D11/12)+(D14/12)+(D17/12)+(D20/12)
Q16Q16=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D11/12)+(D14/12)+(D17/12)+(D20/12)
Q17Q17=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D14/12)+(D17/12)+(D20/12)
Q18Q18=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D17/12)+(D20/12)
Q19Q19=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D20/12)
Q20Q20=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)
Q21Q21=(D38/M9)+(D35/12)+(D26/12)+(D29/12)+(D32/12)
Q22Q22=(D38/M9)+(D35/12)+(D29/12)+(D32/12)
Q23Q23=(D38/M9)+(D35/12)+(D32/12)
Q24Q24=(D38/M9)+(D35/12)
Q25Q25=(D38/M9)
Q26Q26=(D41/M9)
T14:T26T14=Q14-S14
 
Upvote 0
I've added the condition to check for Start months in Column Q, notice I change Q5 to 3/15/2022, so you can see the different results in Column S.

Book3.xlsx
PQRSTUV
4Name Cancel DateOG Mo PPOwing DurationMo. Total
53/15/202212863.338107.921
62/16/20226573.213191.071
72/16/2022910234255.751
8
9
10
11
12
13Current Owing Reduced
14feb31492.43mar446.8231045.61
15mar27025.93apr554.7426471.19
16apr24164.54may554.7423609.8
17may23292.13jun363.6722928.46
18jun20192.76jul107.9220084.84
19jul17857.81aug107.9217749.89
20aug15584.18sep107.9215476.26
21sep13762.83oct107.9213654.91
22oct11715.2nov011715.2
23nov9562.18dec09562.18
24dec5771.53jan05771.53
25jan1095feb1095
26febmar
Sheet1008
Cell Formulas
RangeFormula
S14:S24S14=SUMPRODUCT((MONTH(P14&"/1")+0<=T$5:T$7+1)*(MONTH(P14&"/1")+0>=MONTH(Q$5:Q$7))*U$5:U$7)
T14:T25T14=Q14-S14
 
Upvote 0
When I pop the formula in it is returning as #value. I have modified the table slightly but the formula you have provided it still getting the information from the correct cells.

Sales Orders 2022-2.1 - Blake.xlsm
PQRSTUVWXYZAAAB
4Name Cancel DateOG Mo PPOwing DurationMo. Total
5Bob3/15/202212$863.338$107.921
6Mary2/16/20226573.213$191.071
7Tom2/16/2022910234$255.751
8
9
10
11
12
13Mo12 mo9 mo6 mo3 moOwing 12Owing 9Owing 6Owing 3Reduced 12Reduced 9Reduced 6Reduced 3
14feb$31,492.43$2,386.30#VALUE!#VALUE!$2,386.30
15mar$27,025.93$2,386.30$554.74$26,471.19$2,386.30
16apr$24,164.54$2,386.30$554.74$23,609.81$2,386.30
17may$23,292.13$1,598.78$363.67$22,928.46$1,598.78
18jun$20,192.76$1,360.44$107.92$20,084.84$1,360.44
19jul$17,857.81$853.89$107.92$17,749.89$853.89
20aug$15,584.18$243.89$107.92$15,476.26$243.89
21sep$13,762.83$243.89$107.92$13,654.91$243.89
22oct$11,715.20 $11,715.20 
23nov$9,562.18 $9,562.18 
24dec$5,771.53 $5,771.53 
25jan$1,095.00$1,095.00
26feb  
PP Mo
Cell Formulas
RangeFormula
U5:U7U5=S5/T5
R14R14=C38/M8+(C35/9)+(C23/9)+(C26/9)+(C29/9)+(C20/9)
R15R15=C38/M8+(C35/9)+(C23/9)+(C26/9)+(C29/9)+(C20/9)
R16R16=C38/M8+(C35/9)+(C23/9)+(C26/9)+(C29/9)+(C20/9)
R17R17=C38/M8+(C35/9)+(C23/9)+(C26/9)+(C29/9)
R18R18=C38/M8+(C35/9)+(C26/9)+(C29/9)
R19R19=C38/M8+(C35/9)+(C29/9)
R20R20=C38/M8+(C35/9)
R21R21=C38/M8+(C35/9)
U14U14=SUMPRODUCT((MONTH(P14&"/1")+0<=T$5:T$7+1)*(MONTH(P14&"/1")+0>=MONTH(Q$5:Q$7))*U$5:U$7)
U15:U24U15=SUMPRODUCT((MONTH(P15&"/1")+0<=T$5:T$7+1)*U$5:U$7)
Z14:Z24,Y14:Y25Z14=R14-V14
Q14Q14=(D5/J5)/M9*(J5)+(D8/J8)/M9*(J8)+(D11/J11)/M9*(J11)+(D14/J14)/M9*(J14)+(D17/J17)/M9*(J17)+(D20/J20)/M9*(J20)+(D23/J23)/M9*(J23)+(D26/J26)/M9*(J26)+(D29/J29)/M9*(J29)+(D32/J32)/M9*(J32)+(D35/J35)/M9*(J35)+(D38/J38)/M9*(J38)
Q15Q15=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D8/12)+(D11/12)+(D14/12)+(D17/12)+(D20/12)
Q16Q16=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D11/12)+(D14/12)+(D17/12)+(D20/12)
Q17Q17=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D14/12)+(D17/12)+(D20/12)
Q18Q18=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D17/12)+(D20/12)
Q19Q19=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D20/12)
Q20Q20=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)
Q21Q21=(D38/M9)+(D35/12)+(D26/12)+(D29/12)+(D32/12)
Q22Q22=(D38/M9)+(D35/12)+(D29/12)+(D32/12)
Q23Q23=(D38/M9)+(D35/12)+(D32/12)
Q24Q24=(D38/M9)+(D35/12)
Q25Q25=(D38/M9)
Q26Q26=(D41/M9)
T26T26=Q26-S26
 
Upvote 0
I'm not getting the Error, and can't see a reason why you're getting it.

Book3.xlsx
PQRSTUVWXY
4Name Cancel DateOG Mo PPOwing DurationMo. Total
5Bob3/15/202212863.338107.921
6Mary2/16/20226573.213191.071
7Tom2/16/2022910234255.751
8
9
10
11
12
13Mo12 mo9 mo6 mo3 moOwing 12Owing 9Owing 6Owing 3Reduced 12
14feb31492.43446.8231045.61
15mar27025.93554.7426471.19
16apr24164.54554.7423609.8
17may23292.13363.6722928.46
18jun20192.76107.9220084.84
19jul17857.81107.9217749.89
20aug15584.18107.9215476.26
21sep13762.83107.9213654.91
22oct11715.2011715.2
23nov9562.1809562.18
24dec5771.5305771.53
25jan10951095
26feb
Sheet1015
Cell Formulas
RangeFormula
U14:U24U14=SUMPRODUCT((MONTH(P14&"/1")+0<=T$5:T$7+1)*(MONTH(P14&"/1")+0>=MONTH(Q$5:Q$7))*U$5:U$7)
Y14:Y25Y14=Q14-U14


EDIT: Check for Leading Space in your P Column text months.
 
Upvote 0
I have no idea. I closed and reopened but still showing me the error...I have never had this happen before.

Cell Formulas
RangeFormula
U5:U7U5=S5/T5
S14S14=B38/M7+(B35/6)+(B23/6)+(B26/6)
S15S15=B38/M7+(B35/6)+(B26/6)
S16S16=B38/M7+(B35/6)
S17S17=B38/M7+(B35/6)
S18S18=B38/M7+(B35/6)
S19S19=B38/M7
R14R14=C38/M8+(C35/9)+(C23/9)+(C26/9)+(C29/9)+(C20/9)
R15R15=C38/M8+(C35/9)+(C23/9)+(C26/9)+(C29/9)+(C20/9)
R16R16=C38/M8+(C35/9)+(C23/9)+(C26/9)+(C29/9)+(C20/9)
R17R17=C38/M8+(C35/9)+(C23/9)+(C26/9)+(C29/9)
R18R18=C38/M8+(C35/9)+(C26/9)+(C29/9)
R19R19=C38/M8+(C35/9)+(C29/9)
R20R20=C38/M8+(C35/9)
R21R21=C38/M8+(C35/9)
U14U14=SUMPRODUCT((MONTH(P14&"/1")+0<=T$5:T$7+1)*(MONTH(P14&"/1")+0>=MONTH(Q$5:Q$7))*U$5:U$7)
U15:U24U15=SUMPRODUCT((MONTH(P15&"/1")+0<=T$5:T$7+1)*U$5:U$7)
Z14:Z24,AA14:AA25,Y14:Y25Z14=R14-V14
Q14Q14=(D5/J5)/M9*(J5)+(D8/J8)/M9*(J8)+(D11/J11)/M9*(J11)+(D14/J14)/M9*(J14)+(D17/J17)/M9*(J17)+(D20/J20)/M9*(J20)+(D23/J23)/M9*(J23)+(D26/J26)/M9*(J26)+(D29/J29)/M9*(J29)+(D32/J32)/M9*(J32)+(D35/J35)/M9*(J35)+(D38/J38)/M9*(J38)
Q15Q15=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D8/12)+(D11/12)+(D14/12)+(D17/12)+(D20/12)
Q16Q16=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D11/12)+(D14/12)+(D17/12)+(D20/12)
Q17Q17=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D14/12)+(D17/12)+(D20/12)
Q18Q18=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D17/12)+(D20/12)
Q19Q19=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D20/12)
Q20Q20=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)
Q21Q21=(D38/M9)+(D35/12)+(D26/12)+(D29/12)+(D32/12)
Q22Q22=(D38/M9)+(D35/12)+(D29/12)+(D32/12)
Q23Q23=(D38/M9)+(D35/12)+(D32/12)
Q24Q24=(D38/M9)+(D35/12)
Q25Q25=(D38/M9)
Q26Q26=(D41/M9)
T26T26=Q26-S26



THIS TABLE HAS THE P COLUMN CHANGED TO SHOW THE YEAR - can the formula be modified to reflect this?

Sales Orders 2022-2.1 - Blake.xlsm
PQRSTUVWXYZAAAB
4Name Cancel DateOG Mo PPOwing DurationMo. Total
5Bob3/15/202212$863.338$107.921
6Mary2/16/20226573.213$191.071
7Tom2/16/2022910234$255.751
8
9
10
11
12
13Mo12 mo9 mo6 mo3 moOwing 12Owing 9Owing 6Owing 3Reduced 12Reduced 9Reduced 6Reduced 3
1422-Feb$31,492.43$2,386.30$3,995.42#VALUE!#VALUE!$2,386.30$3,995.42
1522-Mar$27,025.93$2,386.30$2,294.78#VALUE!#VALUE!$2,386.30$2,294.78
1622-Apr$24,164.54$2,386.30$1,084.99#VALUE!#VALUE!$2,386.30$1,084.99
1722-May$23,292.13$1,598.78$1,084.99#VALUE!#VALUE!$1,598.78$1,084.99
1822-Jun$20,192.76$1,360.44$1,084.99#VALUE!#VALUE!$1,360.44$1,084.99
1922-Jul$17,857.81$853.89$340.83#VALUE!#VALUE!$853.89$340.83
2022-Aug$15,584.18$243.89#VALUE!#VALUE!$243.89 
2122-Sep$13,762.83$243.89#VALUE!#VALUE!$243.89 
2222-Oct$11,715.20#VALUE!#VALUE!  
2322-Nov$9,562.18#VALUE!#VALUE!  
2422-Dec$5,771.53#VALUE!#VALUE!  
2523-Jan$1,095.00$1,095.00 
2623-Feb  
2723-Mar
2823-Apr
2923-May
3023-Jun
3123-Jul
3223-Aug
3323-Sep
3423-Oct
3523-Nov
3623-Dec
PP Mo
Cell Formulas
RangeFormula
U5:U7U5=S5/T5
S14S14=B38/M7+(B35/6)+(B23/6)+(B26/6)
S15S15=B38/M7+(B35/6)+(B26/6)
S16S16=B38/M7+(B35/6)
S17S17=B38/M7+(B35/6)
S18S18=B38/M7+(B35/6)
S19S19=B38/M7
R14R14=C38/M8+(C35/9)+(C23/9)+(C26/9)+(C29/9)+(C20/9)
R15R15=C38/M8+(C35/9)+(C23/9)+(C26/9)+(C29/9)+(C20/9)
R16R16=C38/M8+(C35/9)+(C23/9)+(C26/9)+(C29/9)+(C20/9)
R17R17=C38/M8+(C35/9)+(C23/9)+(C26/9)+(C29/9)
R18R18=C38/M8+(C35/9)+(C26/9)+(C29/9)
R19R19=C38/M8+(C35/9)+(C29/9)
R20R20=C38/M8+(C35/9)
R21R21=C38/M8+(C35/9)
U14U14=SUMPRODUCT((MONTH(P14&"/1")+0<=T$5:T$7+1)*(MONTH(P14&"/1")+0>=MONTH(Q$5:Q$7))*U$5:U$7)
U15:U24U15=SUMPRODUCT((MONTH(P15&"/1")+0<=T$5:T$7+1)*U$5:U$7)
Z14:Z24,AA14:AA25,Y14:Y25Z14=R14-V14
Q14Q14=(D5/J5)/M9*(J5)+(D8/J8)/M9*(J8)+(D11/J11)/M9*(J11)+(D14/J14)/M9*(J14)+(D17/J17)/M9*(J17)+(D20/J20)/M9*(J20)+(D23/J23)/M9*(J23)+(D26/J26)/M9*(J26)+(D29/J29)/M9*(J29)+(D32/J32)/M9*(J32)+(D35/J35)/M9*(J35)+(D38/J38)/M9*(J38)
Q15Q15=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D8/12)+(D11/12)+(D14/12)+(D17/12)+(D20/12)
Q16Q16=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D11/12)+(D14/12)+(D17/12)+(D20/12)
Q17Q17=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D14/12)+(D17/12)+(D20/12)
Q18Q18=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D17/12)+(D20/12)
Q19Q19=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D20/12)
Q20Q20=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)
Q21Q21=(D38/M9)+(D35/12)+(D26/12)+(D29/12)+(D32/12)
Q22Q22=(D38/M9)+(D35/12)+(D29/12)+(D32/12)
Q23Q23=(D38/M9)+(D35/12)+(D32/12)
Q24Q24=(D38/M9)+(D35/12)
Q25Q25=(D38/M9)
Q26Q26=(D41/M9)
T26T26=Q26-S26
 
Upvote 0
I have no idea. I closed and reopened but still showing me the error...I have never had this happen before.

THIS TABLE HAS THE P COLUMN CHANGED TO SHOW THE YEAR - can the formula be modified to reflect this?

1. Did you see my comment in Post # 17 to check for Leading Space(s) in Column P text months ?
2. Are those Dates in your New table Column P Real Dates (e.g. numbers, Not Text) ?
 
Upvote 0
Just checked your New Dates in Column P, you need to enter them like, P14 2/1/22 , P15 3/1/22 , then select Both cells and drag down as far as needed.
Custom format cells as yy-mmm if you wish.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,837
Messages
6,181,255
Members
453,028
Latest member
letswriteafairytale

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top