I posted this type of question earlier but it has different nature.
I have a sheet which has 2 columns. One is called company and other is
called "cost". I want a function which will look for a company in the
whole column and and calculate the cost in 2nd column for the company.
Like column one is full with name of compaies(khan And Co, John &
Co,Rich & sons etc). these companies are repeated 50 times in column
one.
I got my answer how to get sum for specific companies. Now the problem
is, I am asked to make a template so any one can use it. Problem is
these companies' name are not always same name, they could be
changed.In january there could be only 3 companies but in march they
could be any 13 companies' with different names. I can calculate total
and make it as a template only if I know which company is being used
but if some one else who does not know any thing abt excel formula and
using my sheet as a template cant get the right total.
=SUMIF(C4:C12,"=khan",D4:D12)
=SUMIF(C4:C12,"=John",D4:D12)
what if some one who is using my template will use "harris" instead of
"khan". Is there any way if user enter the company name and formula
changes its value automatically.

Maybe I've minunderstood something, but could it be as way as:
SUMIF(C2:C3;F1;D2:D3)

Where you enter company name in F1.

Per Erik

On 2 Jun 2005 11:17:10 -0700, "khan" wrote:

I posted this type of question earlier but it has different nature.
I have a sheet which has 2 columns. One is called company and other is
called "cost". I want a function which will look for a company in the
whole column and and calculate the cost in 2nd column for the company.
Like column one is full with name of compaies(khan And Co, John &
Co,Rich & sons etc). these companies are repeated 50 times in column
one.
I got my answer how to get sum for specific companies. Now the problem
is, I am asked to make a template so any one can use it. Problem is
these companies' name are not always same name, they could be
changed.In january there could be only 3 companies but in march they
could be any 13 companies' with different names. I can calculate total
and make it as a template only if I know which company is being used
but if some one else who does not know any thing abt excel formula and
using my sheet as a template cant get the right total.
=SUMIF(C4:C12,"=khan",D4:D12)
=SUMIF(C4:C12,"=John",D4:D12)
what if some one who is using my template will use "harris" instead of
"khan". Is there any way if user enter the company name and formula
changes its value automatically.

khan wrote:
I posted this type of question earlier but it has different nature.
I have a sheet which has 2 columns. One is called company and other is
called "cost". I want a function which will look for a company in the
whole column and and calculate the cost in 2nd column for the company.
Like column one is full with name of compaies(khan And Co, John &
Co,Rich & sons etc). these companies are repeated 50 times in column
one.
I got my answer how to get sum for specific companies. Now the problem
is, I am asked to make a template so any one can use it. Problem is
these companies' name are not always same name, they could be
changed.In january there could be only 3 companies but in march they
could be any 13 companies' with different names. I can calculate total
and make it as a template only if I know which company is being used
but if some one else who does not know any thing abt excel formula and
using my sheet as a template cant get the right total.
=SUMIF(C4:C12,"=khan",D4:D12)
=SUMIF(C4:C12,"=John",D4:D12)
what if some one who is using my template will use "harris" instead of
"khan". Is there any way if user enter the company name and formula
changes its value automatically.


Instead of

=SUMIF(C4:C12,"=khan",D4:D12)

invoke:

=SUMIF($C$4:$C$12,E2,$D$4:$D$12)

where E2 is the cell the user either selects from or enters a company.

One more thought:

Could you set up and Autofilter with a SUBTOTAL function above the data range?

That way users could select the company they want to see and the total for
that company would display above the list. There would be no special
training. Perhaps
just a note above the company list instructing the users to select a company.

--
Regards,
Ron