Select the search type
 
  • Site
  • Web
Search
You are here:   articles
  |  Login

Can find what you are looking for?  Try our custom Google search:

Loading
Previous   BackToCategory    3 of 3
SUMPRODUCT - count how many leap years elapse between two dates
 
Author: Jon von der Heyden
Dated: 20/05/2010
 
Overview:
 
SUMPRODUCT is a very valuable and versatile worksheet function.  This article will describe two useful methods to calculate how many leap years elapse between two given dates.
 
Here are a some rules about SUMPRODUCT that you need to be aware of before we proceed:
  • You cannot pass whole columns/rows to SUMPRODUCT in excel 2003 and earlier versions (so no e.g. A:A).
  • The dimensions of the ranges that you pass in SUMPRODUCT arguments must be equal (i.e. same # of rows/columns).  If they are not SUMPRODUCT result will yield #VALUE!.
  • If any ranges that you pass in SUMPRODUCT arguments contain errors (e.g. #N/A, #REF! etc) then SUMPRODUCT result will yield that same error.
 
Contents:
 
Example table:
 
Sheet1
  A B
1 Start Date End Date
2 2008 2020
3 01/01/2008 31/12/2020
 Download sample workbook.
 
 
 Formula: 
=SUMPRODUCT(--(MOD(ROW(INDIRECT(A2&":"&B2)),4)=0))
 
Breaking it down:
 
ROW(INDIRECT(A2&":"&B2))
This component returns an array of all the year values within the range, i.e:
{2008;2009;2010;2011;2012;2013;2014;2015;2016;2017;2018;2019;2010}

MOD({2008;2009;2010;2011;2012;2013;2014;2015;2016;2017;2018;2019;2010},4)
MOD returns the remainder after a number has been divided by a divisor.  So this will evaluate to:
{0;1;2;3;0;1;2;3;0;1;2;3;0}
 
--({0;1;2;3;0;1;2;3;0;1;2;3;0}=0)
Leap years have a nil remainder after being divided by four.  Hence this will evaluate to:
--{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}
The double unary (--) is used to coerce the TRUE/FALSE values into numeric values (1/0), forcing the array to become:
{1;0;0;0;1;0;0;0;1;0;0;0;1}
 
Finally;
SUMPRODUCT({1;0;0;0;1;0;0;0;1;0;0;0;1})
This will add the values in the array.
 
Result: 4
 
 
 Formula: 
=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A3&":"&B3)),"dd-mm")="29-02"))
 
Breaking it down:
 
ROW(INDIRECT(A3&":"&B3))
This component returns an array of all the date values within the range.  Because serial dates are in fact numbers this could be represented as:
{39448;39449;39450;39451;39452;...........44166}
 
TEXT({39448;39449;39450;39451;39452;...........44166},"dd-mm")
The TEXT function is used to return text formatted date results resulting in the following array:
{"01-01";"02-01";"03-01";"04-01";"05-01";............"31-12"}

--({"01-01";"02-01";"03-01";"04-01";"05-01";............"31-12"}="29-02")
All leap years have an extra day in February.  Hence this will evaluate to:
--({FALSE;FALSE;FALSE;FALSE;.............FALSE})
Note each item in the array that is equal to "29-02" will yield TRUE.
The double unary (--) is used to coerce the TRUE/FALSE values into numeric values (1/0), forcing the array to become:
{0;0;0;0;0;..............0}
Note each item in the array that is TRUE will yield 1.
 
Finally;
SUMPRODUCT({0;0;0;0;0;............0})
This will add the values in the array.
 
Result: 4
 
See Also:
 
 
 
External References:
 
Previous   BackToCategory    3 of 3

home   |   articles   |   downloads   |   about   |   contact