LinkBack URL
About LinkBacks
Good day.
Can someone kindly assist to help create a function that will count the no. of repeat stay per year. Attached is the excel file with an explanation of the desired outcome.
Many thanks in advance.
Count number of repeat stays
Good day.
Can someone kindly assist to help create a function that will count the no. of repeat stay per year. Attached is the excel file with an explanation of the desired outcome.
Many thanks in advance.
Maybe try
=SUMPRODUCT(--($B$3:$B$13>0),--($C$3:$C$13>0)) >> 8
=SUMPRODUCT(--($B$3:$B$13>0),--($C$3:$C$13>0),--($D$3:$D$13>0)) >> 4
HTH
Regards, Jeff
Thanks Jeff!
Thats exactly what I needed. Appreciate the help!
Cheers
You are very welcome and thanks for the feedback
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
Hi Jeff
I will, but I have a follow up request, same file but should I open a new thread?
Is it another count question? If not then a new thread would be good and no need to be sorry.
Hi Jeff
Yes, related to count. I have attached the spreasheet with the desired outcome, not sure if its possible
Thanks again for the help and patience.
Drawing a blank right now on the last part (by year and by resort with max stays), but don't fear, I will call on the formula guru's.
Check out what I have done so far and see if this gets close to what you desire.
If I read the last part right, resort with most no of repeat stay per year, the results should be,
2010 >> MNL >> 6
2011 >> MNL >> 12
2012 >> a tie between BKK and MNL at 3 (What result do you want?)
Also, you profile says 2003 and this is correct?
Hi Jeff
Thanks again, thats what I needed. However, I actually compiled that data manually. In the attached spreadsheet, I have the pivot table and the data source. Just wondering, how should I go about to count those info if the values/data are coming from the pivot table.
Many thanks again for your assistance.
Hi Jeff
I hope I make sense with the request above
The last attachment is the raw data that I have. I put them in pivot table and hopefully be able to create a function that will count the ff:
1. No. of repeat stays per year. (Which I managed to do, manually though)
2. Count the No. of Members with Repeat Stay in year 2010 & 2011 & 2012 / Members with Repeat Stay in year 2010 & 2011 per Tier (which you helped me as per request earlier) but wish to generate count from the pivot table if possible (Attachment post no.10)
Maybe the way I explain it is confusing
Using your sample data from post #7, I have a different definition of a "repeat visitor". That would be someone who has a value > 1 in the column I added to the data. In your sample, only ONE patron has more than one stay in one year.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks