Calculating Current Member Count

[atomictoyguy]

I help run a small club and I am using a spreadsheet to manage our membership. The problem I am trying to solve that I can't seem to find a solution to is as follows.

I currently have a spreadsheet dedicated to membership renewal transactions that has the following columns, ID Number, Transaction Date, Amount Paid, & Expiration Date. I have three named ranges created, ID_NUMBER, TRANSACTION_DATE & EXPIRATION_DATE. The table has about 1700 rows and we typically run 100 - 150 members. I would like to have a table that shows how many members we have each month for each year. I have figured out that that each transaction should count if all conditions of the following have been met.

TRANSACTION_DATE <= MONTH/YEAR
EXPIRATION_DATE >= MONTH/YEAR
ID_NUMBER IS NOT DUPLICATE * This is important as we do have early renewals and I don't want them to count as two members.

Example Data:

TRANSACTION     ID      AMOUNT   EXPIRATION
   DATE       NUMBER     PAID       DATE
 2/26/1995       1      $15.00    2/26/1996
 2/26/1995       2      $15.00    2/26/1996
 2/26/1995       3      $15.00    2/26/1996
  3/1/1995       4      $15.00     3/1/1996
  3/2/1995       5      $15.00     3/2/1996
 3/12/1995       6      $15.00    3/12/1996
 3/26/1995       7      $15.00    3/26/1996
  4/2/1995       8      $15.00     4/2/1996
 4/19/1995       9      $15.00    4/19/1996
  5/3/1995       10     $15.00     5/3/1996
 5/10/1995       2      $15.00    2/26/1997 * Early Renewal Example
 5/10/1995       1      $15.00    2/26/1997 * Early Renewal Example

Example Result:

       1    2    3    4    5    6    7    8    9   10   11   12
      Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep  Oct  Nov  Dec
1995   0    3    6    9   10   10   10   10   10   10   10   10
1996  10   10    9    5    3    2    2    2    2    2    2    2   
1997   2    2    0    0    0    0    0    0    0    0    0    0

I have figured out how to count total transactions per month per year but I can't seem to noodle this out. I have done some searching on your forum and on google. But have yet to find a solution.

Any help will be greatly appreciated!!! Thanks in advance!

If additional details are needed please advise and I will provide as quickly as possible.

 

[Jerry Sullivan]

Hello and Welcome,

I have figured out that that each transaction should count if all conditions of the following have been met.

TRANSACTION_DATE<= MONTH/YEAR
EXPIRATION_DATE >= MONTH/YEAR
ID_NUMBER IS NOT DUPLICATE * This is important as we do have early renewals and I don't want them to count as two members.

Rather than trying to eliminate the duplicate periods for the early renewals, would it work to just key off the period spanning from:
Expiration Date-12 months to Expiration date?

If so, the formula could look like this...

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	START	ID	PAID	EXPIRES			1	2	3
2	2/26/1995	1	$15.00	2/26/1996			JAN	FEB	MAR
3	2/26/1995	2	$15.00	2/26/1996		1995	0	3	7
4	2/26/1995	3	$15.00	2/26/1996		1996	10	12	9
5	3/1/1995	4	$15.00	3/1/1996		1997	2	2	0
Sheet1

Excel 2007

Cell Formulas
Range	Formula
G3	=COUNTIFS($D$2:$D$1700,">="&DATE($F3,G$1,1),$D$2:$D$1700,"<="&DATE($F3+1,G$1+1,0))
H3	=COUNTIFS($D$2:$D$1700,">="&DATE($F3,H$1,1),$D$2:$D$1700,"<="&DATE($F3+1,H$1+1,0))
I3	=COUNTIFS($D$2:$D$1700,">="&DATE($F3,I$1,1),$D$2:$D$1700,"<="&DATE($F3+1,I$1+1,0))

 

[atomictoyguy]

Jerry,

I applied this formula and it seems to work... I haven't double checked the numbers but it looks close enough to me.

Thanks!!!

Also, that formula calculates quickly which I appreciate!!!

Regards,
Lucas