Sumproduct with left and Search

[robcarr]

Hi,

I have this formula which works nearly.

=SUMPRODUCT((Source!$B$2:$B$5000=A13)*(ISNUMBER(SEARCH(B13,Source!$D$2:$D$5000))*(Source!$F$2:$F$5000)))

this sums all values in F range if D and B range values are matched.

However I have some instances of double counting, see below the 2 cells in D range that are causing the problem.

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

you can see call back \ appears in both cells, I only want to use the value where call back \ is at the start of the text string.

I have amended the above formula to this and now all it does is count instances and not sum instances in column F.

=SUMPRODUCT((Source!$B$2:$B$5000=A28)*(ISNUMBER(SEARCH(B28,LEFT(Source!$D$2:$D$5000,LEN(B28)))*Source!$F$2:$F$5000)))

both formulas are being entered as an array.

thanks

Hope this is of use, Rob.

 

[SkipVought]

Rob,

You have A-F, but your posted example represents 3 columns.

Please post SAMPLE REPERESENTATIVE DATE related to this issue, along with the VALUES in the cell references in your formula!

Skip,

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

 

[Deniall]

Don't you have a closing bracket in the wrong place?