Before I jump off my desk...

[drumbumuk]

Problem
I collate results of lead qualification telemarketing in an Excel database.
In the database, amongst other fields in sheet1, I have Campaign Title, Lead
Source Code, Country and Call Status

The lead source code looks like this: UKEMMMYYXXXXXXXXXXTYPE

Where:
UK is always present,
EM could be replaced by WB,
MMYY are MonthYear, X is the campaign name (text of various length)
TYPE is either PROS, CUST or ONLINE

The other key field are:

COUNTRY - is one of three possibilities, UK, RoI, SA
CALL STATUS - could be a number of possibilites but I am interested in Lead
A or Lead B

I need to be able to report on how many Leads (A, B) I have by
EM+COUNTRY+TYPE and WB+COUNTRY+TYPE

I have tried every possibility I can think of, except the right one,
including Pivot Tabels which I know little about.

Any help gratefully recieved.

 

[Roger Govier]

Hi
Add an extra column to your source table assume column X
=LEFT(A1,2)&"|"&MID(A1,3,2)&"|"&RIGHT(A1,4)

Assuming Call Status is column D
Then a Sumproduct
=SUMPRODUCT(--($X$1:$X$1000="EM|UK|PROS"),--($D$1:$D$1000="LEAD A"))

You could set up a report matrix in say columns AA:AC
In AB1 enter Lead A, in AC1 enter Lead B
IN AA2 enter the first of your Analysis groups e.g EM|UK|PROS
and so on down the column.
In AB2 enter
=SUMPRODUCT(--($X$1:$X$1000=$AA2),--($D$1:$D$1000=AB$1))
Copy across to AC2, then copy both cells down as far as required.

 

[Don Guillett]

I'll call you during your dinner to explain it

 

[John C]

=SUMPRODUCT(--(MID($A$2:$A$1000,3,2)=emwb),--($B$2:$B$1000=country),--(RIGHT($A$2:$A$1000,LEN(type))=type),($C$2:$C$1000="Lead A")+($C$2:$C$1000="Lead B"))

This of course assumes that column A has your Lead Source Code, column B has
country, and column C has your Call Status.
I also am just adding Lead A and Lead B, if you need 2 separate tallies,
just modify the formula to remove the + sign and the one you are not solving
for.

 

[John C]

I thought about posting sarcastically as well...

JUMP! JUMP! JUMP! JUMP!