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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,449
Office Version
  1. 2016
Platform
  1. Windows
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)
 

Thlama

New Member
Joined
Mar 24, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Thlama

New Member
Joined
Mar 24, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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)
 

Marcílio_Lobão

Well-known Member
Joined
Oct 7, 2013
Messages
777
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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.
 
Solution

Thlama

New Member
Joined
Mar 24, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Marcílio_Lobão

Well-known Member
Joined
Oct 7, 2013
Messages
777
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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.
 

Thlama

New Member
Joined
Mar 24, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Good morning from Nigeria, Thank you so much Marcilo, Toadstool and MrExcel. The solutions worked perfectly after i became calm and followed the steps.
 

Marcílio_Lobão

Well-known Member
Joined
Oct 7, 2013
Messages
777
Office Version
  1. 2007
Platform
  1. Windows
Thlama, Good morning.

Greetings from BRAZIL.

Thanks for the feedback. Very glad to help you.

Have a nice week-end.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,751
Messages
5,638,150
Members
417,010
Latest member
jnuss03

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
Top