Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Can someone review my my formula logic in the yellow cells? It's pretty self explanatory, just IF and COUNT functions sorting data. Let me know if you have any questions!
Solved by E. L. in 30 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 12/08/2018 - 05:50
Hi
Excelchat Expert 12/08/2018 - 05:50
Welcome to Got it Pro
Excelchat Expert 12/08/2018 - 05:50
Please share the data and tell more about the requirement so that i can help you better
User 12/08/2018 - 05:50
attaching the file!
[Uploaded an Excel file]
Excelchat Expert 12/08/2018 - 05:53
Which formula you want me to review
Excelchat Expert 12/08/2018 - 05:53
Please tell a bit about the requirement
User 12/08/2018 - 05:55
Ieom_plan_type2 dictates if the contract is a Contract Subscription Utility
User 12/08/2018 - 05:56
All of the formulas in the yellow rows are what I need reviewed based on the descriptions in column B
Excelchat Expert 12/08/2018 - 05:56
which cell is it?
User 12/08/2018 - 05:56
Sheet - Top X Column M:M
User 12/08/2018 - 05:57
Sheet Top X has all of the data being sorted
User 12/08/2018 - 05:57
Each Customer has 1 Account ID
User 12/08/2018 - 05:57
And Column C original_year_month is the Billing period
Excelchat Expert 12/08/2018 - 05:58
Yes i can see the formula
Excelchat Expert 12/08/2018 - 05:58
it is counting column C from Top X based on column L being contract in 7th row
Excelchat Expert 12/08/2018 - 05:58
Similarly it is doing for others
User 12/08/2018 - 05:59
Correct
Excelchat Expert 12/08/2018 - 05:59
Then it is finding % in row 14 and others
User 12/08/2018 - 05:59
I just wanted a second set of eyes to review if the logic is correct. It's for a board meeting soon
Excelchat Expert 12/08/2018 - 06:00
in row 36, it is for <1K
Excelchat Expert 12/08/2018 - 06:00
then why have you used >1000
User 12/08/2018 - 06:01
Good catch! That's the mistakes I'm looking for lol
Excelchat Expert 12/08/2018 - 06:02
Similarly in row 52 check there as well
Excelchat Expert 12/08/2018 - 06:03
Others seem to be fine to be
Excelchat Expert 12/08/2018 - 06:03
Let me know if i should specifically look at anything else
Excelchat Expert 12/08/2018 - 06:03
Please let me know if this session helps you
Excelchat Expert 12/08/2018 - 06:03
Looking forward to good rating and reviews from you
User 12/08/2018 - 06:05
Very Helpful! I'm also having difficulty with the logic for a question on the Top X sheet Column Q
Excelchat Expert 12/08/2018 - 06:05
where exactly?
Excelchat Expert 12/08/2018 - 06:06
I dont see a question or a logic there
User 12/08/2018 - 06:06
Q185 I'm looking to see how many customers stayed in the same Tenure from month to month
User 12/08/2018 - 06:06
Column E is the Tenure Rank the Customer is currently in
Excelchat Expert 12/08/2018 - 06:07
so you want how many account IDs were in different tenures?
User 12/08/2018 - 06:08
Count how many account ID were in the same tenure from month to month
Excelchat Expert 12/08/2018 - 06:08
okay, let me build the formula
User 12/08/2018 - 06:08
Thank you!
Excelchat Expert 12/08/2018 - 06:14
Sharing this excel
[Uploaded an Excel file]
Excelchat Expert 12/08/2018 - 06:14
I have done for row 185
Excelchat Expert 12/08/2018 - 06:14
Similarly we can do for others as well
Excelchat Expert 12/08/2018 - 06:15
Have a look and let me know if it helps you out
User 12/08/2018 - 06:15
looking now
Excelchat Expert 12/08/2018 - 06:16
Sure
Excelchat Expert 12/08/2018 - 06:16
For Q186 you can use this
Excelchat Expert 12/08/2018 - 06:16
=COUNTIFS($E$4:$E$238732,">10",$E$4:$E$238732,"<=20",$C$4:$C$238732,Q$2)/COUNT($C$5:$C$238732)
Excelchat Expert 12/08/2018 - 06:16
and then drag right
Excelchat Expert 12/08/2018 - 06:17
Hope this will help you in building for all other rows
User 12/08/2018 - 06:17
Awesome! Yah, I should be able to expand on this.
Excelchat Expert 12/08/2018 - 06:18
Great
Excelchat Expert 12/08/2018 - 06:18
Please do give good rating and reviews when you end the session
User 12/08/2018 - 06:18
Would the logic be different if I am comparing the last 2 and 3 months?
Excelchat Expert 12/08/2018 - 06:18
It really means a lot
Excelchat Expert 12/08/2018 - 06:19
I guess not, logic would be same but i am not sure what that means
Excelchat Expert 12/08/2018 - 06:19
We generally have a policy of 1 ques per session, hope you understand
Excelchat Expert 12/08/2018 - 06:19
If there are multiple questions in a session, we would request to raise another session for that
Excelchat Expert 12/08/2018 - 06:19
I hope that is fine with you
User 12/08/2018 - 06:20
THat's fine. Thank you so much! I will give you 5 Stars plus a good review

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.