Well thanks. It does seem to work but was just wondering if I can have a few
more filtering done to this formula for it to be more effective because the
amount of data that I have is just immense.

What I understand is that I have to put the characters in C1, C2, C3.......
and the formula will match these chracters with the range mentioned and put a
total infront of it.

I can probably have a predetermined sort of list ready in a separate sheet
and can apply that formula infornt of the list of those countries. But the
problem is that I need to do this exercise on a weekly basis and every week
there might be scenarios like the following whereby the data may differ from
week to week:

1- Different countires names might appear in one week when those names were
not in the list in the previous week.

2- Countires names are repeated more than once. An extract is given below:

Bangladish (1)
Bangladish (1)
Bangladish (2)
Bangladish (2)
Bangladish (3)
Bangladish (3)
Bangladish-Chttagong (1)
Bangladish-Chttagong (1)
Bangladish-Chttagong (2)
Bangladish-Chttagong (2)
Bangladish-Chttagong (3)

So can I have the formulas which can do the following for me:

1- Formula which can compare the range of the countries in the current week
with the previous week and list down the new countries list

2- A formula which can refine the list and remove duplicated names.

Using the results of the above formulas, I can come up with a refined list
every week, whereby I can add the new countries (if any) week after week.
Infront of this refined list I can then apply your original formula and get
the magical results :)

Thanks







"Teethless mama" wrote:

Assuming your data in A1:B100
Criterias in:
C1: holds Bangladish
C2: holds Bangladish Mob
and so on....

=SUMPRODUCT(--(LEFT($A$1:$A$100,LEN(C1))=C1),$B$1:$B$100)
copy down

"SAM" wrote:

I have the range of data like the following:

Description

Column A Column B
UAE Mobile 21
UAE 22
Bangladish-Sylhet (2) 33
Bangladish Mob 26
Bangladish Mob (1) 15
Bangladish Mob (2) 29
Bangladish Mob (3) 98

I want the totals of values in column B for the similar values in column A
to be added up. Now I need different stages of totals:

1- I need totals for all the values in column A where the first word is the
same. For example Bangladesh. But Bangladesh is separated from the
remaining text in some cases by a "space" and in some cases by "-".

2- Second I need subtotal for all the values in column A where the first two
words are the same. For example Bangladesh Mobile or Bangladesh-Sylhet. In
these cases the words are separated in the first instance by a "space" and in
the second one by the "-".

3- I want further subtotal where the first three words of the value in
column A are the same and so on and so on.........

Ny help URGENTLY!

Thanks

SAM