Sum of multiple Cells to give date when it meets criterion

Thlama

New Member
Joined
Mar 24, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hi all, i have rainfall data for 3 years. I want a formula that can give me the day of the month once total rainfall from 01 Jan is equal to 51.

DateMONTHDOY
2018​
2019​
2020​
1-Jan​
1​
1​
0​
10.6​
0​
2-Jan​
1​
2​
0​
0​
0​
3-Jan​
1​
3​
0​
0​
0​
4-Jan​
1​
4​
0​
0​
0​
5-Jan​
1​
5​
0​
0​
0​
6-Jan​
1​
6​
0​
0​
0​
7-Jan​
1​
7​
0​
0​
0​
8-Jan​
1​
8​
0​
0​
0​
9-Jan​
1​
9​
0​
0​
0​
10-Jan​
1​
10​
0​
11.2​
0​
11-Jan​
1​
11​
18​
0​
0​
12-Jan​
1​
12​
0​
0​
0​
13-Jan​
1​
13​
0​
0​
0​
14-Jan​
1​
14​
0​
0​
0​
15-Jan​
1​
15​
12.6​
0​
0​
16-Jan​
1​
16​
1.8​
0​
0​
17-Jan​
1​
17​
32.3​
0​
0​
18-Jan​
1​
18​
0​
0​
0​
19-Jan​
1​
19​
0​
0​
0​
20-Jan​
1​
20​
0​
5.2​
0​
21-Jan​
1​
21​
0​
0​
0​
22-Jan​
1​
22​
0​
0​
0​
23-Jan​
1​
23​
12.3​
0​
0​
24-Jan​
1​
24​
0​
0​
0​
25-Jan​
1​
25​
0​
0​
0​
26-Jan​
1​
26​
0​
0​
0​
27-Jan​
1​
27​
2.8​
0​
0​
28-Jan​
1​
28​
0​
11​
0​
29-Jan​
1​
29​
0​
0​
0​
30-Jan​
1​
30​
0​
0​
0​
31-Jan​
1​
31​
0​
3.1​
0​
1-Feb​
2​
32​
0.8​
2​
13​
2-Feb​
2​
33​
0​
0​
0​
3-Feb​
2​
34​
17​
43.6​
0​
4-Feb​
2​
35​
22.7​
0​
0​
5-Feb​
2​
36​
0.6​
0​
0​
6-Feb​
2​
37​
0​
0​
0​
7-Feb​
2​
38​
9.1​
0​
0​
8-Feb​
2​
39​
0​
0​
0​
9-Feb​
2​
40​
0​
0​
0​
10-Feb​
2​
41​
2.3​
0​
0​
11-Feb​
2​
42​
4.2​
0​
0​
12-Feb​
2​
43​
0​
29.7​
0​
13-Feb​
2​
44​
0​
0​
0​
14-Feb​
2​
45​
4.2​
0​
0​
15-Feb​
2​
46​
0.4​
2.4​
0​
16-Feb​
2​
47​
0​
0​
0​
17-Feb​
2​
48​
0​
0​
0​
18-Feb​
2​
49​
18​
0​
0​
19-Feb​
2​
50​
0​
0​
0​
20-Feb​
2​
51​
0​
0​
0​
21-Feb​
2​
52​
0.6​
3.9​
0​
22-Feb​
2​
53​
0​
0​
0​
23-Feb​
2​
54​
0​
0​
0​
24-Feb​
2​
55​
0​
0​
0​
25-Feb​
2​
56​
0​
42​
0​
26-Feb​
2​
57​
0​
2.2​
0​
27-Feb​
2​
58​
1.4​
0​
0​
28-Feb​
2​
59​
0​
9.4​
0​
29-Feb​
2​
60​
0​
0​
0​
1-Mar​
3​
61​
0​
0​
0​
2-Mar​
3​
62​
0​
0​
0​
3-Mar​
3​
63​
8.6​
0​
0​
4-Mar​
3​
64​
12​
102.4​
0​
5-Mar​
3​
65​
0​
0​
0​
6-Mar​
3​
66​
0​
0​
0​
7-Mar​
3​
67​
0​
0​
0​
8-Mar​
3​
68​
0​
1.1​
0​
9-Mar​
3​
69​
3.6​
0​
0​
10-Mar​
3​
70​
6.4​
7.2​
0​
11-Mar​
3​
71​
5.2​
0​
0​
12-Mar​
3​
72​
0​
0​
0​
13-Mar​
3​
73​
0.7​
3.7​
0​
14-Mar​
3​
74​
0​
0​
0​
15-Mar​
3​
75​
8.8​
0​
0​
16-Mar​
3​
76​
0​
0​
0​
17-Mar​
3​
77​
0​
0.6​
0​
18-Mar​
3​
78​
0​
0​
29.7​
19-Mar​
3​
79​
0​
0​
0​
20-Mar​
3​
80​
20.5​
0​
0​
21-Mar​
3​
81​
0​
2.6​
1.4​
22-Mar​
3​
82​
0​
0​
0​
23-Mar​
3​
83​
13.6​
0​
1.5​
24-Mar​
3​
84​
0​
0​
0​
25-Mar​
3​
85​
0​
6.6​
1.1​
26-Mar​
3​
86​
0.7​
0​
0​
27-Mar​
3​
87​
0​
0​
0​
28-Mar​
3​
88​
0​
18.6​
12.7​
29-Mar​
3​
89​
28.9​
0​
0​
30-Mar​
3​
90​
20.9​
2.6​
0​
31-Mar​
3​
91​
0​
0​
0​
1-Apr​
4​
92​
0​
2.9​
42.5​
2-Apr​
4​
93​
2.5​
0​
0​
3-Apr​
4​
94​
0​
0​
6.1​
4-Apr​
4​
95​
20.2​
0​
0​
5-Apr​
4​
96​
28.3​
0​
4.3​
6-Apr​
4​
97​
0​
10.2​
1​
7-Apr​
4​
98​
0​
0​
0​
8-Apr​
4​
99​
0​
0​
0​
9-Apr​
4​
100​
0​
6.6​
0​
10-Apr​
4​
101​
9.3​
3.4​
25.8​
11-Apr​
4​
102​
0​
0​
0​
12-Apr​
4​
103​
0.2​
15​
0​
13-Apr​
4​
104​
0​
25​
12.8​
14-Apr​
4​
105​
17.8​
0​
28.5​
15-Apr​
4​
106​
21.4​
0​
0.9​
16-Apr​
4​
107​
0.3​
1.2​
6.8​
17-Apr​
4​
108​
0.6​
0​
13​
18-Apr​
4​
109​
4.9​
0​
0.8​
19-Apr​
4​
110​
0.8​
25.9​
0​
20-Apr​
4​
111​
0​
21.6​
0​
21-Apr​
4​
112​
0​
0​
0​
22-Apr​
4​
113​
4.5​
14.4​
7​
23-Apr​
4​
114​
0​
18.6​
0​
24-Apr​
4​
115​
0​
1.1​
0​
25-Apr​
4​
116​
0​
0​
16​
26-Apr​
4​
117​
0​
0​
0​
27-Apr​
4​
118​
0​
0​
5.1​
28-Apr​
4​
119​
8.3​
0​
0​
29-Apr​
4​
120​
6.5​
0​
0​
30-Apr​
4​
121​
0​
0​
0​
1-May​
5​
122​
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi THLama,

I'm not sure I've unerstood correctly but is this what you want?

THLama.xlsx
ABCDEFGH
1MONTHDOY201820192020Running TotalResult
21-Jan11010.6010.615
32-Jan1200010.6
43-Jan1300010.6
54-Jan1400010.6
65-Jan1500010.6
76-Jan1600010.6
87-Jan1700010.6
98-Jan1800010.6
109-Jan1900010.6
1110-Jan110011.2021.8
1211-Jan111180039.8
1312-Jan11200039.8
1413-Jan11300039.8
1514-Jan11400039.8
1615-Jan11512.60052.4
1716-Jan1161.80054.2
1817-Jan11732.30086.5
Sheet1
Cell Formulas
RangeFormula
H2H2=DAY(INDEX($A$2:$A$123,MATCH(51,$G$2:$G$123,1)+1))
G2:G18G2=SUM($D$2:$F2)
 
Upvote 0
Not exactly. For example the total rainfall for 2018 from 01 Jan down to the date in 2018 that gives a total of 51. I want a formula that picks out the date automatically.
 
Upvote 0
Not exactly. For example the total rainfall for 2018 from 01 Jan down to the date in 2018 that gives a total of 51. I want a formula that picks out the date automatically.
If you take the total from 01 jan 2018 to 17 Jan 2018, you will have 64.7. This meets the criteria of "51" that is accepted. The date of which is 17 Jan. So the formula should pick 17 Jan as the result.

For 2019, 3rd Feb should be the result since its the date that meets the condition (SUM=51)
 
Upvote 0
Thlana, Good afternoon.

Using the idea of smart colleague toadstool, if you want the account to be done per year, just use an auxiliary column for each year.

The columns can be where you want, using the same suggested method.
AA -> 2018 sum --> AA1:AA123
AB -> 2019 sum --> AB1:AB123
AC -> 2020 sum --> AC1:AC123

You can use a little display to show the results.

26-03-2021_Weather_Control.PNG


J9 ----> =INDEX($A$1:$A$123;MATCH(51;$AA$1:$AA$123;1)+1)

J10 --> =INDEX($A$1:$A$123;MATCH(51;$AB$1:$AB$123;1)+1)

J11 --> =INDEX($A$1:$A$123;MATCH(51;$AC$1:$AC$123;1)+1)

if you want to know what day of the year do:

K9 ----> =INDEX($C$1:$C$123;MATCH(51;$AA$1:$AA$123;1)+1)

K10 --> =INDEX($C$1:$C$123;MATCH(51;$AB$1:$AB$123;1)+1)

K11 --> =INDEX($C$1:$C$123;MATCH(51;$AC$1:$AC$123;1)+1)

Please, tell us if it worked as you wanted.

I hope it helps.
 
Upvote 0
Solution
Thlana, Good afternoon.

Using the idea of smart colleague toadstool, if you want the account to be done per year, just use an auxiliary column for each year.

The columns can be where you want, using the same suggested method.
AA -> 2018 sum --> AA1:AA123
AB -> 2019 sum --> AB1:AB123
AC -> 2020 sum --> AC1:AC123

You can use a little display to show the results.

View attachment 35398

J9 ----> =INDEX($A$1:$A$123;MATCH(51;$AA$1:$AA$123;1)+1)

J10 --> =INDEX($A$1:$A$123;MATCH(51;$AB$1:$AB$123;1)+1)

J11 --> =INDEX($A$1:$A$123;MATCH(51;$AC$1:$AC$123;1)+1)

if you want to know what day of the year do:

K9 ----> =INDEX($C$1:$C$123;MATCH(51;$AA$1:$AA$123;1)+1)

K10 --> =INDEX($C$1:$C$123;MATCH(51;$AB$1:$AB$123;1)+1)

K11 --> =INDEX($C$1:$C$123;MATCH(51;$AC$1:$AC$123;1)+1)

Please, tell us if it worked as you wanted.

I hope it helps.
Thank you so much for the efforts. From the image attached, its exactly the result I need. this should work perfectly. However, can you assist me with an excel mini sheet of it as Toadstool did? It's easier for me as the Ref above has been returning an Error. Thank you once more.
 
Upvote 0
Thlama, Good evening.

"...can you assist me with an excel mini sheet of it as Toadstool did?..."
Unfortunately I cannot install add-ins on this corporate computer.

I'm sending you an image of the sample spreadsheet I made for you.
26-03-2021_Weather_Control-2.PNG
The formulas I already gave you in the previous message.
The process is very simple.

Formulas used at:
AA1 --> =SUM($D$3:D3)
AB1 --> =SUM($E$3:E3)
AC1 --> =SUM($F$3:F3)

...copy them down till line 123

Please, tell us if it solved your question.
I hope it helps.
 
Upvote 0
Thlama, Good morning.

Greetings from BRAZIL.

Thanks for the feedback. Very glad to help you.

Have a nice week-end.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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