• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pull through sales analysis in Excel

N

Nat

New Member
Hi,
First time user here with a question.
I am doing pull through sales analysis for our products that include professional services and subscriptions. I want to see to what extent our professional services business helps to sell subscriptions.
I am looking at the last year sales data. I want to select only those deals that purchased professional services before or together with subscriptions and only if subscriptions are new (not renewals). Can anyone suggest how to identify deals that meet this criteria? I attached a simple example. Thank you in advance!
 

Attachments

Hi Nat

I would put a pivot table over your data with the Row labels as Account, Product Family and Type. Then filter out Renewals. That should be your answer based on my understanding.

Take care

Smallman
 
Thank you for the suggestion, but it is not just about filtering out renewals. I need deals that purchased professional services before or together with subscriptions
 
Hi Nat..

In F2.. try this formula..

=IF(AND(COUNTIFS($A$2:$A$10,A2,$C$2:$C$10,{"Services","Subscription"},$D$2:$D$10,"New")>0),A2,"")

Drag down the same..
Let us know the feedback..
 
Hi,
Thank you for the suggestion, but the formula is not correct. In customer F example, services are not pulling through subscriptions because service sale was after the subscription sale. I need only those deals where services sale preceded subscription sale or happened at the same time. Can the formula be changed to reflect that condition?
 
Hi ,

The data in the file has been changed from what you uploaded ; look at the following data , and indicate which Customers will be displayed :
Code: 
Account Name  Close Month Product Family  Type Sum of Total Price Pulls
Customer A    9/1/2013    Services        NEW    60               Customer A
Customer A    9/1/2013    Subscription    NEW    50               Customer A
Customer B    4/1/2013    Services        NEW    10               Customer B
Customer B    5/1/2013    Subscription    NEW    30               Customer B
Customer C    6/1/2013    Services        NEW    15
Customer D    6/1/2013    Services        NEW    40               Customer D
Customer D    8/1/2013    Subscription    New    40               Customer D
Customer F    8/1/2013    Subscription    NEW    70               Customer F
Customer F    7/1/2013    Services        NEW    80               Customer F
Narayan
 
Oh, I see, the dates for Customer F were changed. I fixed it and the formula works. Thank You!! It is one big formula!!!
 
You must log in or register to reply here.
Back
Top