Hi Guys,
I have a spreadsheet like the example attached that counts the number of sessions that a person has (personal project I'm working on). I want to be able to count the number of sessions a person has had then input a 1,2,3 etc then after 365 days has passed from the first session restart the count - even saying it makes me lose it. I don't know where to begin and if someone could help I would be extremely grateful.
Thanks
I'm a singing coach by the way and building this for myself - not a masseuse
Assuming you have grouped the names together, and the dates are chronological for each group, then try:
=IF(A2<>A1,1,IF(LOOKUP(2,1/((A1:A$2=A2)*(C1:C$2=1)),B1:B$2)<=B2-365,1,C1+1))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Another couple of possibilities.
In D2
=IF(AND(A2=A3,YEAR(B2)=YEAR(B3)),"",SUMIFS(C:C,A:A,A2,B:B,">="&DATE(YEAR(B2),1,1),B:B,"<"&DATE(YEAR(B2)+1,1,1)))
Or as in Sheet2 use SUBTOTAL() then filter by Name and Year or any combination you might need.
In E1
Hope this helps=SUBTOTAL(9,C:C)
If you need any more information, please feel free to ask.
However, if this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
Also
If you are satisfied by any members response to your problem please consider using the small Star icon botom left of thier post to show your appreciation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks