Ok, let's see what this formula is doing.
=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))={1,15}))
We want to count how many 1st and 15th days of the months there are between
a start date and an end date (inclusive).
In order to understand how this formula works you must first understand how
Excel processes dates. Dates are just numbers formatted to look like dates.
In Excel each individual day has a value of 1. The actual date is calculated
by starting from a default base date and then just adding 1 for each
successive day that has passed. By default Excel uses January 1 1900 as the
base date. So, the numeric value for January 1 1990 is 1. This numeric value
is also known as a the date serial number/value.
Since each individual day has a value of 1 we can calculate the date by
counting the number of days since the base date of January 1 1900
(inclusive). Date serial number 1 is January 1 1900, so, date serial number
15 would be January 15 1900. Date serial number 5000 is September 8 1913.
Today is August 9 2008, its date serial number is 39,669. 39,669 days since
January 1 1900 (inclusive).
You can see this by doing the following:
Enter a date in cell A1. By default Excel applies formatting and displays
the date as a DATE. To see its true value, the unformatted date serial
number, change the format of cell A1 to General.
Ok, now let's see how these dates are manipulated in the formula.
=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))={1,15}))
You specifically asked why we need to use INDIRECT. We need to pass the date
serial numbers to the DAY function so it can evaluate them and then in turn
have SUMPRODUCT count how many are the 1st and 15th.
We have a start date and an end date but we also need every individual date
inbetween. Let's assume our dates are:
A1 = start date = 2/27/2008
B1 = end date = 8/4/2008
So, we need to pass to the DAY function an *array* of dates from 2/27/2008
to 8/4/2008. We do that using the ROW function. The ROW function returns an
*array* based on its argument. If you just used:
ROW(A1:B1)
The final result of the formula would be 1. ROW returns an *array* of
numbers based on the *row* number(s) of its argument. In the above, the
returned array would be {1} since the evaluated argument A1:B1 comprises
just the single row, 1. This is where INDIRECT comes into play.
INDIRECT(A1&":"&B1)
INDIRECT takes the dates in A1 and B1 and converts them into a usable
argument that ROW can then evaluate. With our dates:
A1 = 2/27/2008 = date serial number = 39505
B1 = 8/4/2008 = date serial number = 39664
INDIRECT evaluates to: INDIRECT("39505:39664") and converts that TEXT
representation of an argument and passes it to the ROW function so that ROW
evaluates as: ROW($39505:$39664). It's important to know that the argument
passed to ROW must be a valid argument. ROW can't use any value that is
smaller than 1 or larger than the total number of rows an Excel worksheet
can have.
So now we have a means of passing the *array* of dates to the DAY function:
DAY(ROW($39505:$39664)) which evaluates to:
DAY({39505;39506;39507;39508;....39664})
DAY then evalautes these serial numbers and returns an array of day of the
month numbers like this:
DAY(39505) = 27 (2/27/2008)
DAY(39506) = 28 (2/28/2008)
DAY(39507) = 29 (2/29/2008)
DAY(39508) = 1 (3/1/2008)
...
...
DAY(39664) = 4 (8/4/208)
DAY({27;28;29;1;.....4})
This array of day numbers is then evalauted to be equal to 1 or 15
=SUMPRODUCT(--({27;28;29;1;.....4}={1,15}))
This evaluation returns an array of either TRUE or FALSE:
27 = {1,15} = F,F
28 = {1,15} = F,F
29 = {1,15} = F,F
1 = {1,15} = T,F
etc etc
Since SUMPRODUCT can't use these logical values we need to convert them to
numbers which SUMPRODUCT then sums to arrive at our final result. One way to
convert the logicals to numbers is to use the double unary operator "--". It
coerces TRUE to 1 and FALSE to 0.
We now have:
=SUMPRODUCT({0,0;0,0;0,0;1,0;...etc})
So:
A1 = 2/27/2008
B1 = 8/4/2008
=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))={1,15}))
=11
Also, when I highlight <INDIRECT(A1&":"&B1 )> in the
formula bar and press F9, I get an error message which says
"Formula is too long. Formulas may not exceed 8192 characters."
You must be using Excel 2007 and a large date span. Use a smaller date span.
Try it with a span of just a "few" days and then it'll work.
