Count members with different memberships

[Charlie_]

Hej.

I am running a gym, and we have 3 different type of memberships.
a yearly membership, a half year membership and a 2 month membership.
I would like to keep track of members, how many I have in any given month.
So I am trying to find a way, to subtract members from a running total, when their membership runs out.
Our memberships default to running from specific dates, to specific dates, so not from bought date.

I could make a list of dates, and check to see if their membership is active, but it would be a lot of data.

best regards
Charlie

 

[Joe4]

Welcome to the Board!

Your question is quite general, and we would need to know more specifics in order to help you come up with a solution, namely we would need to know the structure of your data.
Could you post a sample of what your data actually looks like?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Charlie_]

Sorry for missing out on added some data.
This is a qucik snippet of the data, and a refrence ark over all the data type:_

Book1
	A	B	C	D	E	F	G	H	I
1	Date/time	Membership	Sub-Style	affiliation			Membership name	Sub-Style	affiliation
2	17-06-2020	Membership - associate (2)	Yearly	associate			Membership - student () (1)	Half-yearly	student ()
3	17-06-2020	Membership - associate (2)	Yearly	associate			Membership - student () (2)	Yearly	student ()
4	17-06-2020	Membership - associate (2)	Yearly	associate			Membership - student () (3)	Mounthly	student ()
5	17-06-2020	Membership - associate (2)	Yearly	associate			Membership - student () (4)	Mounthly	student ()
6	17-06-2020	Membership - associate (2)	Yearly	associate			Membership - associate (1)	Half-yearly	associate
7	01-02-2021	Membership - student () (1)	Half-yearly	student ()			Membership - associate (2)	Yearly	associate
8	01-02-2021	Membership - student () (1)	Half-yearly	student ()			Membership - associate (3)	Mounthly	associate
9	01-02-2021	Membership - student () (1)	Half-yearly	student ()			Membership - associate (4)	Mounthly	associate
10	01-02-2021	Membership - student () (1)	Half-yearly	student ()			Membership - non- (1)	Half-yearly	non-
11	17-06-2020	Membership - associate (2)	Yearly	associate			Membership - non- (2)	Yearly	non-
12	01-12-2020	Membership - student () (4)	Mounthly	student ()			Membership - non- (3)	Mounthly	non-
13	01-12-2020	Membership - student () (4)	Mounthly	student ()			Membership - non- (4)	Mounthly	non-
14	01-12-2020	Membership - student () (4)	Mounthly	student ()			Membership - student (Non-) (1)	Half-yearly	student ()
15	01-12-2020	Membership - student () (4)	Mounthly	student ()			Membership - student (Non-) (2)	Yearly	student ()
16	17-06-2020	Membership - student () (1)	Half-yearly	student ()			Membership - student (Non-) (3)	Mounthly	student ()
17	17-06-2020	Membership - associate (2)	Yearly	associate			Membership - student (Non-) (4)	Mounthly	student ()
Sheet1

Cell Formulas
Range	Formula
C2:C17	C2	=VLOOKUP(B2,Table73,2,FALSE)
D2:D17	D2	=VLOOKUP(B2,Table7,3,FALSE)

 

[Joe4]

One way to do this would be to add a "helper" column, and Expiration Date column. Let's say we do that in column E.
So put this formula in cell E2 and copy down to row 17:

=EDATE(A2,LOOKUP(C2,{"Half-yearly","Mounthly","Yearly"},{6,1,12}))

Now, let's say that we have a date in cell E19, and we want to count how many active gym members we have on that day.
Then that formula might look like this:

=COUNTIFS($A$2:$A$17,"<=" & E19,$E$2:$E$17,">=" & E19)