Subtract value over multiple cells

B

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
Sort by date Sort by votes
I did change the column P to the yy-mmm format but I am not sure about the Leading Space in P. I am still receiving the #value! error..this is a headscratcher


Sales Orders 2022-2.1 - Blake with dates.xlsm
PQRSTUVWXYZAAAB
4Name Cancel DateOG Mo PPOwing DurationMo. Total
5Bob02-15-202212$863.338$107.921
6Mary02-16-20226573.213$191.071
7Tom03-15-2022910234$255.751
8
9
10
11
12
13Mo12 mo9 mo6 mo3 moOwing 12Owing 9Owing 6Owing 3Reduced 12Reduced 9Reduced 6Reduced 3
14Feb-22$31,492.43$2,386.30$3,995.42#VALUE!#VALUE!$2,386.30$3,995.42
15Mar-22$27,025.93$2,386.30$2,294.78#VALUE!#VALUE!$2,386.30$2,294.78
16Apr-22$24,164.54$2,386.30$1,084.99#VALUE!#VALUE!$2,386.30$1,084.99
17May-22$23,292.13$1,598.78$1,084.99#VALUE!#VALUE!$1,598.78$1,084.99
18Jun-22$20,192.76$1,360.44$1,084.99#VALUE!#VALUE!$1,360.44$1,084.99
19Jul-22$17,857.81$853.89$340.83#VALUE!#VALUE!$853.89$340.83
20Aug-22$15,584.18$243.89#VALUE!#VALUE!$243.89 
21Sep-22$13,762.83$243.89#VALUE!#VALUE!$243.89 
22Oct-22$11,715.20#VALUE!#VALUE!  
23Nov-22$9,562.18#VALUE!#VALUE!  
24Dec-22$5,771.53#VALUE!#VALUE!  
25Jan-22$1,095.00$1,095.00 
26Feb-22  
27Mar-22
28Apr-22
29May-22
30Jun-22
31Jul-22
32Aug-22
33Sep-22
34Oct-22
35Nov-22
36Dec-22
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

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
No, after you enter Real Dates in Column P, I'll need to modify my formula to account for that.
A bit tied up right now, will work on it a little later.
 
Upvote 0
Thank you help, I appreciate it.

I have cleaned up the date fields and inputted some more information. Here is the latest updated version.

Sales Orders 2022-2.1 - Blake with dates.xlsm
PQRSTUVWXYZAAAB
4Name Cancel DateOG Mo PPOwing DurationMo. Total
5Bob02-15-202212$863.338$107.921
6Mary02-16-20226573.213$191.071
7Tom03-15-2022910234$255.751
8
9
10
11
12
13Mo12 mo9 mo6 mo3 moOwing 12Owing 9Owing 6Owing 3Reduced 12Reduced 9Reduced 6Reduced 3
14Feb-22$31,492.43$2,386.30$3,995.42$5,089.31#VALUE!#VALUE!$2,386.30$3,995.42$5,089.31
15Mar-22$27,025.93$2,386.30$2,294.78$5,089.31#VALUE!#VALUE!$2,386.30$2,294.78$5,089.31
16Apr-22$24,164.54$2,386.30$1,084.99$2,177.19#VALUE!#VALUE!$2,386.30$1,084.99$2,177.19
17May-22$23,292.13$1,598.78$1,084.99#VALUE!#VALUE!$1,598.78$1,084.99 
18Jun-22$20,192.76$1,360.44$1,084.99#VALUE!#VALUE!$1,360.44$1,084.99 
19Jul-22$17,857.81$853.89$340.83#VALUE!#VALUE!$853.89$340.83 
20Aug-22$15,584.18$243.89#VALUE!#VALUE!$243.89  
21Sep-22$13,762.83$243.89#VALUE!#VALUE!$243.89  
22Oct-22$11,715.20#VALUE!#VALUE!   
23Nov-22$9,562.18#VALUE!#VALUE!   
24Dec-22$5,771.53#VALUE!#VALUE!   
25Jan-23$1,095.00#VALUE!#VALUE! 
26Feb-23  
27Mar-23
28Apr-23
29May-23
30Jun-23
31Jul-23
32Aug-23
33Sep-23
34Oct-23
35Nov-23
36Dec-23
PP Mo
Cell Formulas
RangeFormula
U5:U7U5=S5/T5
T14T14=A38/M6+(A35/3)
T15T15=A38/M6+(A35/3)
T16T16=A35/M6
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)
Z14:Z24,AA14:AA25,Y14:Y25,AB14:AB24Z14=R14-V14
U14U14=SUMPRODUCT((MONTH(M12&"/1")+0<=T$5:T$7+1)*U$5:U$7)
U15:U25U15=SUMPRODUCT((MONTH(P15&"/1")+0<=T$5:T$7+1)*U$5:U$7)
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
Didn't download your latest sheet, seems every time I check this post, you have a new version.
This is based on where we left off.
I need you to create a new set of values, as I show in W4:W7

Book3.xlsx
PQRSTUVWXY
4Name Cancel DateOG Mo PPOwing DurationMo. TotalCancel End Date
5Bob3/15/202212863.338107.92111/15/2022
6Mary2/16/20226573.213191.0715/16/2022
7Tom2/16/2022910234255.7516/16/2022
8
9
10
11
12
13Mo12 mo9 mo6 mo3 moOwing 12Owing 9Owing 6Owing 3Reduced 12
1422-Feb31492.43446.8231045.61
1522-Mar27025.93554.7426471.19
1622-Apr24164.54554.7423609.8
1722-May23292.13363.6722928.46
1822-Jun20192.76107.9220084.84
1922-Jul17857.81107.9217749.89
2022-Aug15584.18107.9215476.26
2122-Sep13762.83107.9213654.91
2222-Oct11715.2011715.2
2322-Nov9562.1809562.18
2422-Dec5771.5305771.53
2523-Jan109501095
2623-Feb0
2723-Mar0
2823-Apr0
2923-May0
3023-Jun0
3123-Jul0
3223-Aug0
3323-Sep0
3423-Oct0
3523-Nov0
3623-Dec0
Sheet1018
Cell Formulas
RangeFormula
W5:W7W5=EDATE(Q5,T5)
Y14:Y25Y14=Q14-U14
U14:U36U14=SUMPRODUCT((MONTH(P14)<=T$5:T$7+1)*(MONTH(P14)>=MONTH(Q$5:Q$7))*(YEAR(P14)<=YEAR(W$5:W$7))*U$5:U$7)
 
Upvote 0
Hi again, I followed everything as instructed but I am still getting a #value! return

I am not too sure what I am doing wrong. (This is the sheet that you were working on...not an updated version)

Thank you, again

Sales Orders 2022-2.1 - Blake with dates 2.0.xlsm
OPQRSTUVWXY
3
4Name Cancel DateOG Mo PPOwing DurationMo. TotalCancel End Date
5Bob3/15/202212$863.338$107.921#VALUE!
6Mary2/16/20226573.213$191.071#VALUE!
7Tom2/16/2022910234$255.751#VALUE!
8
9
10
11
12
13Mo12 mo9 mo6 mo3 moOwing 12Owing 9Owing 6Owing 3Reduced 12
1422-Feb$31,492.43#VALUE!#VALUE!
1522-Mar$27,025.93#VALUE!#VALUE!
1622-Apr$24,164.54#VALUE!#VALUE!
1722-May$23,292.13#VALUE!#VALUE!
1822-Jun$20,192.76#VALUE!#VALUE!
1922-Jul$17,857.81#VALUE!#VALUE!
2022-Aug$15,584.18#VALUE!#VALUE!
2122-Sep$13,762.83#VALUE!#VALUE!
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
W5:W7W5=EDATE(Q5,T5)
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)
Y14:Y25Y14=Q14-U14
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 GOT IT TO WORK! - just made a few tweaks. THANK YOU

But there is just one issue, the formula continues into new year. I change T7 to a duration of 12 months and as you see from U14:U36 the numbers aren't populating properly.


Sales Orders 2022-2.1 - Blake with dates 2.0.xlsm
OPQRSTUVWXY
3
4Name Cancel DateOG Mo PPOwing DurationMo. TotalCancel End Date
5Bob01-Feb-2212$863.338$107.9212022-10-01
6Mary02-Mar-226573.213$191.0712022-06-02
7Tom03-Feb-229102312$85.2512023-02-03
8
9
10
11
12
13Mo12 mo9 mo6 mo3 moOwing 12Owing 9Owing 6Owing 3Reduced 12
1422-Feb$31,492.43$2,386.30$3,995.42$193.17$31,299.27
1522-Mar$27,025.93$2,386.30$2,294.78$384.24$26,641.69
1622-Apr$24,164.54$2,386.30$1,084.99$384.24$23,780.31
1722-May$23,292.13$1,598.78$1,084.99$193.17$23,098.96
1822-Jun$20,192.76$1,360.44$1,084.99$193.17$19,999.59
1922-Jul$17,857.81$853.89$340.83$193.17$17,664.64
2022-Aug$15,584.18$243.89$193.17$15,391.01
2122-Sep$13,762.83$243.89$193.17$13,569.66
2222-Oct$11,715.20$85.25$11,629.95
2322-Nov$9,562.18$85.25$9,476.93
2422-Dec$5,771.53$85.25$5,686.28
2523-Jan$1,095.00 $1,095.00
2623-Feb  $193.17
2723-Mar$384.24
2823-Apr$384.24
2923-May$193.17
3023-Jun$193.17
3123-Jul$193.17
3223-Aug$193.17
3323-Sep$193.17
3423-Oct$85.25
3523-Nov$85.25
3623-Dec$85.25
37
PP Mo
Cell Formulas
RangeFormula
U5:U7U5=S5/T5
W5:W7W5=EDATE(Q5,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)
Y14:Y25Y14=Q14-U14
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
U14:U36U14=SUMPRODUCT((MONTH(P14)<=T$5:T$7+1)*(MONTH(P14)>=MONTH(Q$5:Q$7))*(YEAR(P14)<=YEAR(W$5:W$7))*U$5:U$7)
 
Upvote 0
Blake0920 said:
I GOT IT TO WORK! - just made a few tweaks. THANK YOU

But there is just one issue, the formula continues into new year. I change T7 to a duration of 12 months and as you see from U14:U36 the numbers aren't populating properly.
Click to expand...

Your dates in Column P are Wrong, see my comments in my Post #20:

jtakw said:
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.
Click to expand...

The Dates you have in Column P, if you format it as just Date, are All in 2022 like this:

Book3.xlsx
P
13Mo
142/22/2022
153/22/2022
164/22/2022
175/22/2022
186/22/2022
197/22/2022
208/22/2022
219/22/2022
2210/22/2022
2311/22/2022
2412/22/2022
251/23/2022
262/23/2022
273/23/2022
284/23/2022
295/23/2022
306/23/2022
317/23/2022
328/23/2022
339/23/2022
3410/23/2022
3511/23/2022
3612/23/2022
Sheet1020


It was getting late for me last night, and I didn't fully test my formula before posting, this modified version should work correctly:

Book3.xlsx
PQRSTUVWXY
4Name Cancel DateOG Mo PPOwing DurationMo. TotalCancel End Date
5Bob2/1/202212863.338107.92110/1/2022
6Mary3/2/20226573.213191.0716/2/2022
7Tom2/3/2022910231285.2512/3/2023
8
9
10
11
12
13Mo12 mo9 mo6 mo3 moOwing 12Owing 9Owing 6Owing 3Reduced 12
142/1/202231492.43193.1731299.26
153/1/202227025.93384.2426641.69
164/1/202224164.54384.2423780.3
175/1/202223292.13384.2422907.89
186/1/202220192.76193.1719999.59
197/1/202217857.81193.1717664.64
208/1/202215584.18193.1715391.01
219/1/202213762.83193.1713569.66
2210/1/202211715.285.2511629.95
2311/1/20229562.1885.259476.93
2412/1/20225771.5385.255686.28
251/1/2023109585.251009.75
262/1/20230
273/1/20230
284/1/20230
295/1/20230
306/1/20230
317/1/20230
328/1/20230
339/1/20230
3410/1/20230
3511/1/20230
3612/1/20230
Sheet1019
Cell Formulas
RangeFormula
W5:W7W5=EDATE(Q5,T5)
Y14:Y25Y14=Q14-U14
U14:U36U14=SUMPRODUCT((EOMONTH(P14,0)>=Q$5:Q$7)*(EOMONTH(P14,0)<=W$5:W$7)*U$5:U$7)
 
Last edited:
Upvote 0
No I apricate it, unfortunally I am still getting errors. I have put everything into proper formatting but still am getting #value!

i formatted W5:W7 as mm-dd-yyyy



Sales Orders 2022-2.1 - Blake with dates 2.0.xlsm
PQRSTUVWXY
4Name Cancel DateOG Mo PPOwing DurationMo. TotalCancel End Date
5Bob2/1/202212$863.338$107.921#VALUE!
6Mary3/2/20226$573.213$191.071#VALUE!
7Tom2/3/20229$1,023.0012$85.251#VALUE!
8
9
10
11
12
13Mo12 mo9 mo6 mo3 moOwing 12Owing 9Owing 6Owing 3Reduced 12
142/1/2022$31,492.43$2,386.30$3,995.42#VALUE!#VALUE!
153/1/2022$27,025.93$2,386.30$2,294.78#VALUE!#VALUE!
164/1/2022$24,164.54$2,386.30$1,084.99#VALUE!#VALUE!
175/1/2022$23,292.13$1,598.78$1,084.99#VALUE!#VALUE!
186/1/2022$20,192.76$1,360.44$1,084.99#VALUE!#VALUE!
197/1/2022$17,857.81$853.89$340.83#VALUE!#VALUE!
208/1/2022$15,584.18$243.89#VALUE!#VALUE!
219/1/2022$13,762.83$243.89#VALUE!#VALUE!
2210/1/2022$11,715.20#VALUE!#VALUE!
2311/1/2022$9,562.18#VALUE!#VALUE!
2412/1/2022$5,771.53#VALUE!#VALUE!
251/1/2023$1,095.00#VALUE!#VALUE!
262/1/2023  #VALUE!
273/1/2023#VALUE!
284/1/2023#VALUE!
295/1/2023#VALUE!
306/1/2023#VALUE!
317/1/2023#VALUE!
328/1/2023#VALUE!
339/1/2023#VALUE!
3410/1/2023#VALUE!
3511/1/2023#VALUE!
3612/1/2023#VALUE!
PP Mo
Cell Formulas
RangeFormula
U5:U7U5=S5/T5
W5:W7W5=EDATE(Q5,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)
Y14:Y25Y14=Q14-U14
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
U14:U36U14=SUMPRODUCT((EOMONTH(P14,0)>=Q$5:Q$7)*(EOMONTH(P14,0)<=W$5:W$7)*U$5:U$7)
 
Upvote 0
I copied your sheet in above post, made No changes, I'm getting correct results with No #VALUE! errors in Column U ( highlighted )
All the other errors are dependent of cells somewhere else on your sheet which I have no access or knowledge of it's content.
Regardless, my formula in Column U ( U14:U36 ) is Independent of these values elsewhere on your sheet, and should work correctly.
I Don't know why you're getting the #VALUE! error.

Book3.xlsx
PQRSTUVWXY
4Name Cancel DateOG Mo PPOwing DurationMo. TotalCancel End Date
5Bob2/1/202212863.338107.9163110/1/2022
6Mary3/2/20226573.213191.0716/2/2022
7Tom2/3/2022910231285.2512/3/2023
8
9
10
11
12
13Mo12 mo9 mo6 mo3 moOwing 12Owing 9Owing 6Owing 3Reduced 12
142/1/2022#DIV/0!#DIV/0!#DIV/0!193.1663#DIV/0!
153/1/2022#DIV/0!#DIV/0!#DIV/0!384.2363#DIV/0!
164/1/2022#DIV/0!#DIV/0!#DIV/0!384.2363#DIV/0!
175/1/2022#DIV/0!#DIV/0!#DIV/0!384.2363#DIV/0!
186/1/2022#DIV/0!#DIV/0!#DIV/0!193.1663#DIV/0!
197/1/2022#DIV/0!#DIV/0!#DIV/0!193.1663#DIV/0!
208/1/2022#DIV/0!#DIV/0!193.1663#DIV/0!
219/1/2022#DIV/0!#DIV/0!193.1663#DIV/0!
2210/1/2022#DIV/0!85.25#DIV/0!
2311/1/2022#DIV/0!85.25#DIV/0!
2412/1/2022#DIV/0!85.25#DIV/0!
251/1/2023#DIV/0!85.25#DIV/0!
262/1/2023#DIV/0!#DIV/0!0
273/1/20230
284/1/20230
295/1/20230
306/1/20230
317/1/20230
328/1/20230
339/1/20230
3410/1/20230
3511/1/20230
3612/1/20230
Sheet1022
Cell Formulas
RangeFormula
U5:U7U5=S5/T5
W5:W7W5=EDATE(Q5,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)
Y14:Y25Y14=Q14-U14
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
U14:U36U14=SUMPRODUCT((EOMONTH(P14,0)>=Q$5:Q$7)*(EOMONTH(P14,0)<=W$5:W$7)*U$5:U$7)
 
Upvote 0
Try copying P4:Y36 to a NEW CLEAN SHEET in range P4:Y36 (without any of your Other cells/columns) and see what happens with U14:U36 results.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Auto update the date range
Replies
3
Views
248
kvsrinivasamurthy
K
V
  • Solved
Average Function
Replies
8
Views
395
Peter_SSs
Peter_SSs
G
  • Question Question
Vstack a variable range of 1D vertical arrays
Replies
3
Views
443
dreid1011
dreid1011
K
  • Question Question
VBA codes to replace all cells containing a certain value
Replies
2
Views
429
Snakehips
Snakehips
MrDB4Excel
  • Solved
Get the Correct Month
2
Replies
10
Views
173
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,836
Messages
6,181,252
Members
453,028
Latest member
letswriteafairytale

Share this page

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