formula to add values for all the same first words in cells in a r

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Ragdyer]

I believe the easiest solution is for you to use "Auto Filter"!

Check it out in the Help files, and post back with any questions.

 

[Guest]

Well I have tried using the auto filter but it requires lot of manual
intervention to find the totals for all the countries, while the using the
forlula provided by "teethless mama" gives me the totals just by copying the
formula infront of the names list.

Do you think macro can help in this thing?

 

[vezerid]

There are a lot of things you are looking for and I am only visiting
the NG presently for a short time, so I might not be around for the
followup...

One thing that can help you is to identify the delimiter points. I.e.
when the first word ends, when the second one ends etc.

The following *array* formula will find the position of the first non-
letter (i.e. delimiter, be it space, dash or whatever). In B1:

=MIN(IF(ISERROR(SEARCH(MID(A1,ROW($1:$100),
1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),ROW($1:$100)))

In C1 (and copied to D1):

=MIN(IF(ISERROR(SEARCH(MID($A1,ROW($1:$100),
1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),B1+1),ROW($1:$100)))

Now you have these delimiters (although for items like Bangladesh (1)
we might need a different strategy). You can use them in the following
manner:

=LEFT(A1,B1-1)

I know it is not a complete solution but it might be a start.

HTH
Kostis Vezerides