SUMIF where conditions vary depending on text string

[David]

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

[NBVC]

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.

[David]

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.

[NBVC]

How about?

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

[David]

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!

[NBVC]

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

[David]

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

[NBVC]

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

[David]

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!

[Hercules1946]

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.