Sum of multiple Cells to give date when it meets criterion

Thlama · Mar 26, 2021

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.

Date	MONTH	DOY	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

Toadstool · Mar 26, 2021

Hi THLama,

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

THLama.xlsx

A

B

C

D

E

F

G

H

1

MONTH

DOY

2018

2019

2020

Running Total

Result

2

1-Jan

1

0

10.6

0

10.6

15

3

2-Jan

1

2

0

10.6

4

3-Jan

1

3

0

10.6

5

4-Jan

1

4

0

10.6

6

5-Jan

1

5

0

10.6

7

6-Jan

1

6

0

10.6

8

7-Jan

1

7

0

10.6

9

8-Jan

1

8

0

10.6

10

9-Jan

1

9

0

10.6

11

10-Jan

1

10

0

11.2

0

21.8

12

11-Jan

1

11

18

0

39.8

13

12-Jan

1

12

0

39.8

14

13-Jan

1

13

0

39.8

15

14-Jan

1

14

0

39.8

16

15-Jan

1

15

12.6

0

52.4

17

16-Jan

1

16

1.8

0

54.2

18

17-Jan

1

17

32.3

0

86.5

Sheet1

Cell Formulas
Range	Formula
H2	H2	=DAY(INDEX($A$2:$A$123,MATCH(51,$G$2:$G$123,1)+1))
G2:G18	G2	=SUM($D$2:$F2)

Thlama · Mar 26, 2021

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 · Mar 26, 2021

Thlama said:
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 · Mar 26, 2021

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.

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.

Thlama · Mar 26, 2021

Marcílio_Lobão said:
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 · Mar 26, 2021

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.

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 · Mar 27, 2021

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 · Mar 27, 2021

Thlama, Good morning.

Greetings from BRAZIL.

Thanks for the feedback. Very glad to help you.

Have a nice week-end.

Sum of multiple Cells to give date when it meets criterion

Thlama

New Member

Excel Facts

Toadstool

Well-known Member

Thlama

New Member

Thlama

New Member

Marcílio_Lobão

Well-known Member

Thlama

New Member

Marcílio_Lobão

Well-known Member

Thlama

New Member

Marcílio_Lobão

Well-known Member

Similar threads

Forum statistics

Share this page

Sum of multiple Cells to give date when it meets criterion

New Member

Excel Facts

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock