Sumproduct, ISnumber and Search query

[robcarr]

HI,

I am using the formula below to get some data for me.

{=SUMPRODUCT((Source!$B$2:$B$5000=A14)*(ISNUMBER(SEARCH(B14,Source!$D$2:$D$5000)))*(Source!$F$2:$F$5000))}

I have noticed a limitation in the formula and I can't figure out how to get the data.

I am searching for this string within a cell "Call Back \" what I want to do is search for the value at the start of the string.

The string that is being searched is an export from a file and I dont want to edit this as it makes the report easier for people to use. The string has 3 parts each part is a level from the system, the problem is Call back can appear at level 2 as well as level 1.

String examples

Admin Line \ Customer Call Back \ Left Answer Phone Message
Call Back \ Incoming Whitemail \ Case Updated

I need to only count level 1 at this point with formula.

So how do I edit the formula to get the desired result.





Hope this is of use, Rob.

 

[SkipVought]

hi,

it would help if you would post some sample data and what you expect your formula to return from that example.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I have no idea what your ISNUMBER(SEARCH( thingy is trying to do, not knowing what is in B14 OR what is in column D -- you have us in the DARK!!!

this does a conditional SUM on column F.
[tt]
=SUMPRODUCT((LEFT(Source!$B$2:$B$5000,9)=A14)*(Source!$F$2:$F$5000))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}