Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: SUMIF where conditions vary depending on text string

  1. #1

    SUMIF where conditions vary depending on text string



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi there,

    I would really appreciate some help with this as I am getting to the end of my tether with it.

    I’ve created the formulae in the 4 columns in the attachedspreadsheet with yellow headers. I need the last two columns to countconversions and revenue where the channel path doesn’t end either:
    … > Paid Search
    Or
    … > Direct (where Paid Search is the last channel beforeDirect)
    The formulas I have here do it fine, except where Paid Search isthe last channel before Direct and there has been more than 1 Direct visitafterwards, e.g.
    … > Paid Search > Direct > Direct
    Therecould also be a potentially infinite number of Directs.

    Can anyone help me? Any guidance would be very much appreciated.

    Thanks

    David
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Can you give a sample of expected results and how you arrived at them? Maybe repost the workbook showing expected results.

    Also, if you have posted the question elsewhere, please provide links, so we can follow.


  3. #3
    Thanks for getting back to me.

    I've attached another version of the workbook with less data. Where the rows have no fill colour, the current formula deliver the expected results. The rows which are coloured in orange are those where the formula does not produce the expected results. Under the Click Assisted Bookings and Click Assisted Revenue columns of the orange rows, I have deleted the current formula and added in the value I would expect to be returned, 0 and £0.00 in all cases.

    I haven't posted this anywhere else yet, but if I do I will ensure to keep you in the loop.
    Attached Files Attached Files

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    How about?

    =SUMIFS(B3,A3,"*Paid*",F3,"<>*Paid Search > Direct*",F3,"<>*Direct > Direct*",E3,"<>*Paid*")


  5. #5
    That seems to work except in situations such as below:

    Paid Search > Organic Search > Direct > Direct

    Where there has been a channel other than Paid Search in-between that and the Direct visits. This should be counted but the formula would rule this out.

    It's a step closer though, thank you!

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    I get a 0 if that is in column A. You are saying it shouldn't.


  7. #7
    I get a 0 if that is in column A as well. However, I need to count instances where the channel path doesn’t end either "> Paid Search" or "> Direct" where Paid Search is the last channel before Direct.

    For the text string:

    Paid Search > Organic Search > Direct > Direct

    Organic Search is the last channel before the Direct visits however, Paid Search has also featured in the channel path and is neither the last channel nor the last channel before the Direct visits and should therefore be counted.

    Does that make sense?

    Thanks again

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    This seems to work... but I don't know if you have other combinations....


    =SUMIFS(B3,A3,"*Paid*",F3,"<>*Paid Search > Direct*",A3,"<>**Paid Search > Direct > Direct*",E3,"<>*Paid*")


  9. #9
    Thank you very much for your help. That more or less catches them all. There's a few further down the list that it rules out which it should count, such as

    Paid Search > Direct > Direct > Direct > Direct > Organic Search > Direct > Direct

    and

    Paid Search > Direct > Direct > Direct > Organic Search > Direct > Direct

    but I can easily filter the results and sense check these to make sure I catch them at the end.

    I appreciate it!

  10. #10
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by David View Post
    Thank you very much for your help. That more or less catches them all. There's a few further down the list that it rules out which it should count, such as

    Paid Search > Direct > Direct > Direct > Direct > Organic Search > Direct > Direct

    and

    Paid Search > Direct > Direct > Direct > Organic Search > Direct > Direct

    but I can easily filter the results and sense check these to make sure I catch them at the end.

    I appreciate it!
    If I was wanting a formula that will catch all, I would try to make a complete list of the conditions that when TRUE mean that the conversions should be counted, and build it from there.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •