Counting Total Cells (Blank and Filled) Between Dates

[magnethero]

Hello all,

First time posting in an Excel forum and I have been racking my brain for the last two days trying to get this to work. My goal is that I'm trying to create a summary page for all departments of a large series of information for years back. To start I want to count the number of cells between each year (always in the same column) but because the information is always added to the top, the data shows something like this:

[TABLE="width: 750"]
<tbody>[TR]
[TD]Dept #[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2018[/TD]
[TD]Dept Name[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Staff Type[/TD]
[TD]Staff Type[/TD]
[TD]Staff Type[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Sum Value[/TD]
[TD]Sum Value[/TD]
[TD]Sum Value[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Changes[/TD]
[TD]Merged[/TD]
[TD]Merged[/TD]
[TD]Merged[/TD]
[TD]Value Change[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The cells between the year values are a mix of filled, blank, and merged cells and the years go in reverse order. I'm not sure how to tackle this. Some ideas were to use a =countifs() with a > and < range of the years but this failed because of the reverse order of the years.

The largest hurdle is I need it to count at the next change in year. If it counted all the values between 2018 and say 2015, the summary wouldn't work properly as it would show amounts only valid for the whole change in company. If anyone has any suggestions, please let me know!

Thank you.

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Dept #[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Cells between years:[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td]

2018​

[/td][td]Dept Name[/td][td][/td][td][/td][td][/td][td]

2018​

[/td][td]

5​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td][/td][td]Staff Type[/td][td]Staff Type[/td][td]Staff Type[/td][td][/td][td]

2017​

[/td][td]

2​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td]Sum Value[/td][td]Sum Value[/td][td]Sum Value[/td][td][/td][td]

2016​

[/td][td]

3​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

2015​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]Changes[/td][td]Merged[/td][td]Merged[/td][td]Merged[/td][td]Value Change[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td][/td][td]

2017​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td][/td][td]

2016​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td][/td][td]

2015​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet19[/td][/tr][/table]

Formula in cell H3:
=MATCH(G4,$B$1:$B$21,0)-MATCH(G3,$B$1:$B$21,0)-1

Copy cell H3 and paste to cells below as far as needed.