Count blank cells between related cells in a range

[germandiazm]

Hi,

I need to count the blank cells between row on column A that matches the date in cell G2.

So when I update the date on cell G2, the result of the formula will show how many days left until harvest commences.

So result in C2 should be 3, and in E2 should be 15.

						Thank you for your help! German

<colgroup><col><col span="7"></colgroup><tbody>
</tbody>

<colgroup><col><col span="6"></colgroup><tbody></tbody>

 

[germandiazm]

Thanks Peter,

The first formula worked really well, I'll just have to modify it to work even when the date in G2 is not yet on the array or when there's no value on the column, but that is just some tweaking I'll need to play with.

I couldn't make the second option work =MAX(AGGREGATE(15,6,$A4:$A34/(B4:B34<>""),1)-$G2,0), it returned a 0 value in all cases.


Thanks

 

[Peter_SSs]

... even when the date in G2 is not yet on the array ..

For the sample data in post 10, do you mean if G2 is, say, 5 August 2018?
If so, what do you want the formula to return in that case?

... or when there's no value on the column

What do you want the formula to return in that case?

I couldn't make the second option work =MAX(AGGREGATE(15,6,$A4:$A34/(B4:B34<>""),1)-$G2,0), it returned a 0 value in all cases.

I thought this might be how that formula would work. In columns B, E & F the earliest date in the column is before the G2 date, so i thought you might not want negative values showing in those columns. What do you want in a circumstance like those columns?

Excel Workbook
	A	B	C	D	E	F	G
1	Pen Number	25a	6a	4a	3a	2
2	Days til harvest	0	1	3	0	0	Wed 18 Jul 18
3	Harvest Date
4	Sun 1 Jul 18
5	Mon 2 Jul 18
6	Tue 3 Jul 18
7	Wed 4 Jul 18
8	Thu 5 Jul 18
9	Fri 6 Jul 18
10	Sat 7 Jul 18				15000
11	Sun 8 Jul 18
12	Mon 9 Jul 18
13	Tue 10 Jul 18
14	Wed 11 Jul 18
15	Thu 12 Jul 18
16	Fri 13 Jul 18
17	Sat 14 Jul 18
18	Sun 15 Jul 18
19	Mon 16 Jul 18	17,528
20	Tue 17 Jul 18					19,934
21	Wed 18 Jul 18					15,651
22	Thu 19 Jul 18	17,331	904
23	Fri 20 Jul 18		16,892
24	Sat 21 Jul 18		15,976	7,632
25	Sun 22 Jul 18			20,837
26	Mon 23 Jul 18			2,227
27	Tue 24 Jul 18
Days

 

[germandiazm]

For the sample data in post 10, do you mean if G2 is, say, 5 August 2018?
If so, what do you want the formula to return in that case?

Is when I'm preparing the info for the next couple of months, so G2 could be 15/July/2018 but the sheet is for the month of September.

What do you want the formula to return in that case?

A blank cell

I thought this might be how that formula would work. In columns B, E & F the earliest date in the column is before the G2 date, so i thought you might not want negative values showing in those columns. What do you want in a circumstance like those columns?

Cell	Formula
B2	=MAX(AGGREGATE(15,6,$A4:$A34/(B4:B34<>""),1)-$G2,0)

<tbody>
</tbody>

A 0 instead of a negative is perfect, but when I tried the formula it returned 0 to all cells, including the ones that should have been positive values.

 

[Peter_SSs]

Is when I'm preparing the info for the next couple of months, so G2 could be 15/July/2018 but the sheet is for the month of September.

That didn't tell me what you want returned in that circumstance.
What is wrong with these results? (Note also that this formula with MAX in it is not returning all 0 values)

Excel Workbook
	A	B	C	D	E	F	G
1	Pen Number	25a	6a	4a	3a	2
2	Days til harvest	63	66	68	54	64	Sun 15 Jul 18
3	Harvest Date
4	Sat 1 Sep 18
5	Sun 2 Sep 18
6	Mon 3 Sep 18
7	Tue 4 Sep 18
8	Wed 5 Sep 18
9	Thu 6 Sep 18
10	Fri 7 Sep 18				15000
11	Sat 8 Sep 18
12	Sun 9 Sep 18
13	Mon 10 Sep 18
14	Tue 11 Sep 18
15	Wed 12 Sep 18
16	Thu 13 Sep 18
17	Fri 14 Sep 18
18	Sat 15 Sep 18
19	Sun 16 Sep 18	17,528
20	Mon 17 Sep 18					19,934
21	Tue 18 Sep 18					15,651
22	Wed 19 Sep 18	17,331	904
23	Thu 20 Sep 18		16,892
24	Fri 21 Sep 18		15,976	7,632
25	Sat 22 Sep 18			20,837
26	Sun 23 Sep 18			2,227
27	Mon 24 Sep 18
28	Tue 25 Sep 18
29	Wed 26 Sep 18
30	Thu 27 Sep 18
31	Fri 28 Sep 18
32	Sat 29 Sep 18
33	Sun 30 Sep 18
Days

A blank cell

OK, so we'll just add an IFERROR to it

Excel Workbook
	A	B	C	D	E	F	G
1	Pen Number	25a	6a	4a	3a	2
2	Days til harvest	63		68		64	Sun 15 Jul 18
3	Harvest Date
4	Sat 1 Sep 18
5	Sun 2 Sep 18
6	Mon 3 Sep 18
7	Tue 4 Sep 18
8	Wed 5 Sep 18
9	Thu 6 Sep 18
10	Fri 7 Sep 18
11	Sat 8 Sep 18
12	Sun 9 Sep 18
13	Mon 10 Sep 18
14	Tue 11 Sep 18
15	Wed 12 Sep 18
16	Thu 13 Sep 18
17	Fri 14 Sep 18
18	Sat 15 Sep 18
19	Sun 16 Sep 18	17,528
20	Mon 17 Sep 18					19,934
21	Tue 18 Sep 18					15,651
22	Wed 19 Sep 18	17,331
23	Thu 20 Sep 18
24	Fri 21 Sep 18			7,632
25	Sat 22 Sep 18			20,837
26	Sun 23 Sep 18			2,227
27	Mon 24 Sep 18
28	Tue 25 Sep 18
29	Wed 26 Sep 18
30	Thu 27 Sep 18
31	Fri 28 Sep 18
32	Sat 29 Sep 18
33	Sun 30 Sep 18
Days

A 0 instead of a negative is perfect, but when I tried the formula it returned 0 to all cells, including the ones that should have been positive values.

Here it is again, seems to me to be what you are asking for.
- Number of days when the first value in column is after the G2 data (columns C & D)
- 0 When the first value in the column is before the G2 date (columns B & F)
- Blank when there is nothing in the column (column E)

Excel Workbook
	A	B	C	D	E	F	G
1	Pen Number	25a	6a	4a	3a	2
2	Days til harvest	0	8	3		0	Tue 18 Sep 18
3	Harvest Date
4	Sat 1 Sep 18
5	Sun 2 Sep 18
6	Mon 3 Sep 18
7	Tue 4 Sep 18
8	Wed 5 Sep 18
9	Thu 6 Sep 18
10	Fri 7 Sep 18
11	Sat 8 Sep 18
12	Sun 9 Sep 18
13	Mon 10 Sep 18
14	Tue 11 Sep 18
15	Wed 12 Sep 18
16	Thu 13 Sep 18
17	Fri 14 Sep 18
18	Sat 15 Sep 18
19	Sun 16 Sep 18	17,528
20	Mon 17 Sep 18					19,934
21	Tue 18 Sep 18					15,651
22	Wed 19 Sep 18	17,331
23	Thu 20 Sep 18
24	Fri 21 Sep 18			7,632
25	Sat 22 Sep 18			20,837
26	Sun 23 Sep 18			2,227
27	Mon 24 Sep 18
28	Tue 25 Sep 18
29	Wed 26 Sep 18		5,000
30	Thu 27 Sep 18
31	Fri 28 Sep 18
32	Sat 29 Sep 18
33	Sun 30 Sep 18
Days

 

[germandiazm]

Hi Peter,

Thank you so much for your help, that is perfect!

Just in case, in my previous message I didn't mean that the result on your example was wrong, I meant that when I used it I was getting the wrong result (I had made a mistake in the formula).

Again, thank you very much!

German.

 

[Peter_SSs]

Hi Peter,

Thank you so much for your help, that is perfect!

Just in case, in my previous message I didn't mean that the result on your example was wrong, I meant that when I used it I was getting the wrong result (I had made a mistake in the formula).

Again, thank you very much!

German.

You are welcome. Glad you got it sorted. Thanks for letting us know.