=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


"drumbumuk" wrote:

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

Whe
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 a

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.