SUMIF where conditions vary depending on text string

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:[FONT=&quot][/FONT]
… > Paid Search[FONT=&quot][/FONT]
Or[FONT=&quot][/FONT]
… > Direct (where Paid Search is the last channel beforeDirect)[FONT=&quot][/FONT]
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.[FONT=&quot][/FONT]
… > Paid Search > Direct > Direct[FONT=&quot][/FONT]
[FONT=&quot]Therecould also be a potentially infinite number of Directs.

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

Thanks

David[/FONT]

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.

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.

How about?

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

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!

I get a 0 if that is in column A. You are saying it shouldn't.

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

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*")

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!

David said:

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!

Click to expand...

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.

The problem I have with that is there is no limit on the number of Direct visits I would need to rule out. Even if I listed all of the conditions I would need to filter in this data set, a future data set may contain a pattern that was not contained here.

I've obviously used the below part of the formula to say cell F3 "does not contain":

F3,"<>*Paid Search > Direct*"

Is it possible to say the text string "does not end with"?

Thanks again

David said:

The problem I have with that is there is no limit on the number of Direct visits I would need to rule out. Even if I listed all of the conditions I would need to filter in this data set, a future data set may contain a pattern that was not contained here.

I've obviously used the below part of the formula to say cell F3 "does not contain":

F3,"<>*Paid Search > Direct*"

Is it possible to say the text string "does not end with"?

Thanks again

Click to expand...

David
THe problem is that your trying to resolve this by asking if a certain task can be acheived in a formula. Because I have no idea what your objectives are, and what the data means, I can't formulate a solution and all I can do is answer your questions. You on the other hand have already shown that you can inspect a string, and say straight away whether the conversions should be included or not. All you need to do is to convey that decision process to your helper, who will then be able to suggest a solution.

One other question: Have you considered including COL A as a criteria range, as I think that this is what your using when your verifying the results?

Answering your question in post #11:

F3, "<>*Paid Search > Direct"
If the above is included as a criteria range, criteria combination, it will not be counted if it ends with the string stated.