Jake wants to know, given an active date and an inactive date, how to count the customers that were active in a certain time period.
The ones we want are highlighted in yellow. The formula is
It’s easier to figure out who is not active during that date range and subtract it from the total. The formula starts by counting everyone using the COUNT function. The SUMPRODUCT function is then subtracted from that. It gives the total of all the customers who became active after our End date plus the customers who became inactive before our Start date.
Note that this formula excludes the actual Start and End date. Customer 17 isn’t included because we’re really looking at 7/16/2010 to 7/24/2010. If you want the formula to be inclusive, simply change the <= and >= to < and >, respectively.
If you don’t like SUMPRODUCT, you can get there with COUNTIF
Sometimes it’s easier to turn the problem around and figure out who’s excluded.