Count blank cells between related cells in a range

germandiazm

New Member
Joined
May 9, 2017
Messages
11
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.

h3wykXtKCqUtfCuDriw8LdYrxIu50L8J_ALTmar283N30TkiA2GQVj3wbzPcwK9j6cBKw214gJ4mWGIxmmQVHa_Nnvnw6G50HXq8IjBhC_BucKTyblFYSOBSH7DSeBibCy0eyz-VhCIHfxVWqjPQI9wc8NbpDpwQvUdlPkUDvN6Ffb6O6LraXbf-92DrkZedxDIQqku8B8TkNAOyyLroVG3FG1wvYW1Kt3IRJt4CXRP9RdJGFrOUfbXxIRvtl8QwWxtPaBq6NoYCxq2dEVkEWEG88bylHqJ09CEgTllzoMKteaGire6Octdi1sfDFd2OKK6exAUSlbmCjsEPIuYwcf2DfuYOZl9je0LLiepusUNn4kDpLRZz3JLgBJvgadWwZopJHaR3QH6VGUM6IwFwHjPcnQIS_w6UmGZAfgyPKBDWrRty9TITfg-DvCnuSpxjtBhFZ6BXQbe4mMfCtuTs2USLkWAEWXT_8HD3M77pEAr8IhwIwYPRXkGrjmkxpdDK40WApoGc9BI5p2I00dY5wl8HTxU76fGZ3A8ALRB2DkTfLC8FwEJFssrVnfMUXhLaH2_drob1B5DCegEP0BR4q_wX57LX8G7aSO9R0Zo=w489-h657-no


Thank you for your help!

German

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

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

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Not sure what you've done here regardng attachments but I see no spreadsheet image, just a grey "No Entry" sign

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.
 
Last edited:
Upvote 0
Thanks Special-K99, image hosting service I used before (Google Photots) only shows the pic while I'
m signed in. Here's the screenshot:

286CPWd
 
Upvote 0
First value on column C is on row 22, date on first column on row 22 is 19/July and date in cell G2 is 16/July; 3 days between those dates.

First value in column E is on the 31/July, number of days between date on G2 and A34 is 15.
 
Upvote 0
in C2
=MIN(IF(C4:C35<>"",ROW(C4:C35)))-MATCH(G2,A4:A35)
Array formula, use Ctrl-Shift-Enter

and copy to E2

This assumes that the dates in column A are actually dates (not text) and the value in G2 is a date (not text).
I say this because the format for column A values is different to the format of cell G2 - so if either of them are text this won't work.
 
Last edited:
Upvote 0
Third time lucky?
Sort of, but we can't copy/paste data from an image like that so many potential helpers will just pass by your thread as they don't want to spend a lot of time typing out the data to test.
Far better to use one of the methods suggested by Special-K99, especially the 'Attachments' link.
 
Upvote 0
Hi Peter, thank you for the recommendation, it makes sense.

Special-K99, could you please check what I've done wrong? the results are not correct.

I've put the formula on row 3. For example, in column C, the numbers of rows between the row matching the date on G2 and the first non blank cell should be 3, but result is showing 6.

ABCDEFG
1Pen Number25a6a4a3a2
2Days til harvest02414116-Jul
3Harvest Date368184
4Sun 1 Jul 18
5Mon 2 Jul 18
6Tue 3 Jul 18
7Wed 4 Jul 18
8Thu 5 Jul 18
9Fri 6 Jul 18
10Sat 7 Jul 18
11Sun 8 Jul 18
12Mon 9 Jul 18
13Tue 10 Jul 18
14Wed 11 Jul 18
15Thu 12 Jul 18
16Fri 13 Jul 18
17Sat 14 Jul 18
18Sun 15 Jul 18
19Mon 16 Jul 18 17,528
20Tue 17 Jul 18 19,934
21Wed 18 Jul 18 15,651
22Thu 19 Jul 18 17,331 904
23Fri 20 Jul 18 - 16,892
24Sat 21 Jul 18 15,976 7,632
25Sun 22 Jul 18 20,837
26Mon 23 Jul 18 2,227
27Tue 24 Jul 18
28Wed 25 Jul 18
29Thu 26 Jul 18
30Fri 27 Jul 18
31Sat 28 Jul 18
32Sun 29 Jul 18
33Mon 30 Jul 18
34Tue 31 Jul 18 6,255
35

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
July 2018

Array Formulas
CellFormula
B3{=MIN(IF(B4:B35<>"",ROW(B4:B35)))-MATCH($G$2,$A$4:$A$35)}
C3{=MIN(IF(C4:C35<>"",ROW(C4:C35)))-MATCH($G$2,$A$4:$A$35)}
D3{=MIN(IF(D4:D35<>"",ROW(D4:D35)))-MATCH($G$2,$A$4:$A$35)}
E3{=MIN(IF(E4:E35<>"",ROW(E4:E35)))-MATCH($G$2,$A$4:$A$35)}
F3{=MIN(IF(F4:F35<>"",ROW(F4:F35)))-MATCH($G$2,$A$4:$A$35)}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0
See if this, copied across does what you want.

Excel Workbook
ABCDEFG
1Pen Number25a6a4a3a2
2Days til harvest035151Mon 16 Jul 18
3Harvest Date
4Sun 1 Jul 18
5Mon 2 Jul 18
6Tue 3 Jul 18
7Wed 4 Jul 18
8Thu 5 Jul 18
9Fri 6 Jul 18
10Sat 7 Jul 18
11Sun 8 Jul 18
12Mon 9 Jul 18
13Tue 10 Jul 18
14Wed 11 Jul 18
15Thu 12 Jul 18
16Fri 13 Jul 18
17Sat 14 Jul 18
18Sun 15 Jul 18
19Mon 16 Jul 1817,528
20Tue 17 Jul 1819,934
21Wed 18 Jul 1815,651
22Thu 19 Jul 1817,331904
23Fri 20 Jul 18-16,892
24Sat 21 Jul 1815,9767,632
25Sun 22 Jul 1820,837
26Mon 23 Jul 182,227
27Tue 24 Jul 18
28Wed 25 Jul 18
29Thu 26 Jul 18
30Fri 27 Jul 18
31Sat 28 Jul 18
32Sun 29 Jul 18
33Mon 30 Jul 18
34Tue 31 Jul 186,255
Days



If you don't want the formula to return negative values once the date has passed, try

=MAX(AGGREGATE(15,6,$A4:$A34/(B4:B34<>""),1)-$G2,0)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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