Hello folks!
I’ve an array where each row is a day of the year (Jan 1 - Dec 31) and columns are the year.
Cells are populated with text only (cities names).
How do I know e.g. in the last 12-months period starting from today (today date should be updated every file opening) how many of those cells are populated?
A non blank cell count basically but in a specific date range.
Is there someone that can help me with array formulas?
Merry XMas!
This is an interesting question. But, unfortunately, you will not be able to get an exact answer without very complex calculations. The day of February 29 is to blame; its presence or absence in the year will distort the result of the formula. And to take into account the leap year, the formula will have to be very complicated. You should either come to terms with the fact that the result will differ slightly from the true value, or change the table with cities and dates so as to avoid appearing on February 29 in a non-leap year and not to miss this day in a leap year.
My first thought was the same, but now I think that 29.02 brings no any complications to calculations if you consider “last 12-months” or “during last year” period as a period between two same dates in two consecutive years (inclusive or exclusive first or last day of the period, just need to define it before and keep it in mind after), but not as 365/366 days before today (which will prompt you to find, if this period shall include 29.02 or not). If I understand author correctly, first column is all possible dates in year, so 29.02 is included as the possible option, but it stays empty almost forever 
And the same date is always on the same row. So one just needs to count populated cells below the current row in the previous column and above the current row in the current column. So during non-leap years 29.02 will not affect calculations, cause it definitely will be empty, but if in a leap year it is not empty - that means you shall count it.
@ SM_Riga
You’re correct on that: leap year has to be considered and 29.02 isn’t to blame since it gonna be empty in non leap years.
First column could be any possible year, and yes I need just to count the populated cells.
Sounds a bit complicated in a spreadsheet and I evaluated also a possible solution using a database that gives me more flexibility on queries, but that adds some other complications (not related to the database but the environment).
Thanks.
If you promise that on February 29, 2018 you was not go anywhere, then let’s count the cities.
Naming convention: To simplify the formula a bit, let’s assign names to the work ranges, as shown in this picture.
The first step is very simple - to find the offset value from the column days to the desired year.
For last year and this year it will be very similar, differ only by -1:
MATCH(YEAR(TODAY())-1;YearsRow;0)
MATCH(YEAR(TODAY());YearsRow;0)
Finding the string with the current date will be not much more difficult:
MATCH(TEXT(TODAY();"MMM D");DaysColumn;0)
Now you can easily determine the ranges for counting - take the whole column of names of days, move right to the desired year and cut off the excess:
OFFSET(DaysColumn;MATCH(TEXT(TODAY();"MMM D");DaysColumn;0);MATCH(YEAR(TODAY())-1;YearsRow;0);ROWS(DaysColumn);1)
OFFSET(DaysColumn;0;MATCH(YEAR(TODAY());YearsRow;0);MATCH(TEXT(TODAY();"MMM D");DaysColumn;0);1)
Please note: it is assumed that there are no other data below the table of cities. Otherwise, the first formula will be a little more complicated - to limit the height of the OFFSET() range, you need to once again calculate the position of the current date using the MATCH()
Now, using the usual COUNTA(), count the number of non-empty cells in each of the subranges and sum up the values obtained
=COUNTA(
OFFSET(DaysColumn;
MATCH(TEXT(TODAY();"MMM D"); DaysColumn;0);
MATCH(YEAR(TODAY())-1;YearsRow;0);
ROWS(DaysColumn);1))
+ COUNTA(
OFFSET(DaysColumn;
0;
MATCH(YEAR(TODAY());YearsRow;0);
MATCH(TEXT(TODAY();"MMM D");DaysColumn;0);1))
Edit by @SM_Riga on 25.12.2018 19:55 with the permission of @JohnSUN
Please also find Demo spreadsheet with above mentioned functionality and also with how to make formulas easier demonstration, based on the different data structure.
With the permission of @JohnSUN I will add demo spreadsheet to his answer which demonstrates a bit this functionality and also shows how much easier is make same calculations if data is arranged consecutive in one column. Made it for this topic, but there is already no need to make a new answer