Search of a Formula/Function
 

Dear Sir,

I am in seacrh of a formula. I want to fill up the cell of COST-TOm and
COST-Harry.

Product COST-TOM COST-Harry
Apple ??? ???
Mango ??? ???
Banana ??? ???

Data Available
Farmer Product COST
Tom Apple 10
Harry Apple 20
Harry Banana 7
Tom Apple 10
Harry Mango 30
Harry Apple 20
Tom Mango 15
Harry Mango 30

Regards,
Nimish

Search of a Formula/Function
 

You want a pivot table. It's perfect for your application. Look here for help:
http://www.cpearson.com/excel/pivots.htm


--
Regards,
Fred


"Nimish Shah" wrote in message
...
Dear Sir,

I am in seacrh of a formula. I want to fill up the cell of COST-TOm and
COST-Harry.

Product COST-TOM COST-Harry
Apple ??? ???
Mango ??? ???
Banana ??? ???

Data Available
Farmer Product COST
Tom Apple 10
Harry Apple 20
Harry Banana 7
Tom Apple 10
Harry Mango 30
Harry Apple 20
Tom Mango 15
Harry Mango 30

Regards,
Nimish

Search of a Formula/Function
 

You will use an array formula to do this, which means you must hit
CTL+Shift+Enter when you are done inputting it instead of just Enter. For
COST-TOM the formula
=SUM(IF($A$7:$A$10&$B$7:$B$10=$B$1&$A2,$C$7:$C$10) )
this assumes your data available begins in A7 and product end in B10. For
COST-Harry the formula would be
=SUM(IF($A$7:$A$10&$B$7:$B$10=$C$1&$A2,$C$7:$C$10) )
if you don't see {} around your formula when you are done you didn't hit
CTL+Shift+Enter.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Nimish Shah" wrote:

Dear Sir,

I am in seacrh of a formula. I want to fill up the cell of COST-TOm and
COST-Harry.

Product COST-TOM COST-Harry
Apple ??? ???
Mango ??? ???
Banana ??? ???

Data Available
Farmer Product COST
Tom Apple 10
Harry Apple 20
Harry Banana 7
Tom Apple 10
Harry Mango 30
Harry Apple 20
Tom Mango 15
Harry Mango 30

Regards,
Nimish

Search of a Formula/Function
 

Hi Fred,

Thank you but i have never worked with Pivot Tables. Is there a formula with
IF, sumproduct etc etc. which can help. In the meantime i am trying to learn
Pivot Table.

Regards,

Nimish


"Fred Smith" wrote:

You want a pivot table. It's perfect for your application. Look here for help:
http://www.cpearson.com/excel/pivots.htm


--
Regards,
Fred


"Nimish Shah" wrote in message
...
Dear Sir,

I am in seacrh of a formula. I want to fill up the cell of COST-TOm and
COST-Harry.

Product COST-TOM COST-Harry
Apple ??? ???
Mango ??? ???
Banana ??? ???

Data Available
Farmer Product COST
Tom Apple 10
Harry Apple 20
Harry Banana 7
Tom Apple 10
Harry Mango 30
Harry Apple 20
Tom Mango 15
Harry Mango 30

Regards,
Nimish

Search of a Formula/Function
 

=SUMPRODUCT(--($A$2:$A$20="Tom"),--($B$2:$B$20="Apple"))

etc.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Nimish Shah" wrote in message
...
Dear Sir,

I am in seacrh of a formula. I want to fill up the cell of COST-TOm and
COST-Harry.

Product COST-TOM COST-Harry
Apple ??? ???
Mango ??? ???
Banana ??? ???

Data Available
Farmer Product COST
Tom Apple 10
Harry Apple 20
Harry Banana 7
Tom Apple 10
Harry Mango 30
Harry Apple 20
Tom Mango 15
Harry Mango 30

Regards,
Nimish

Search of a Formula/Function
 

Pivot tables are easier to grasp than formulas, and they are certainly
easier to set up and maintain than a bunch of formulas. Why reinvent
the wheel when Excel already has a built-in feature that does what you
want perfectly?


On Dec 27, 9:56 am, Nimish Shah
wrote:
Hi Fred,

Thank you but i have never worked with Pivot Tables. Is there a formula with
IF, sumproduct etc etc. which can help. In the meantime i am trying to learn
Pivot Table.

Regards,

Nimish

"Fred Smith" wrote:
You want a pivot table. It's perfect for your application. Look here for help:
http://www.cpearson.com/excel/pivots.htm

--
Regards,
Fred

"Nimish Shah" wrote in message
...
Dear Sir,

I am in seacrh of a formula. I want to fill up the cell of COST-TOm and
COST-Harry.

Product COST-TOM COST-Harry
Apple ??? ???
Mango ??? ???
Banana ??? ???

Data Available
Farmer Product COST
Tom Apple 10
Harry Apple 20
Harry Banana 7
Tom Apple 10
Harry Mango 30
Harry Apple 20
Tom Mango 15
Harry Mango 30

Regards,
Nimish

Search of a Formula/Function
 

John,

Thank you. The formula works fine but it sums up the cost. Actual in the
data table the cost if fixed as given below, i need this figure in the
cost-tom and cost-harry:-
TOM-APPLE-10
TOM-BANANA-5
TOM-MANGO-15
HARRY-APPLE-20
HARRY-BANANA-7
HARRY-MANGO-30

Regards,

Nimish

***************************

"John Bundy" wrote:

You will use an array formula to do this, which means you must hit
CTL+Shift+Enter when you are done inputting it instead of just Enter. For
COST-TOM the formula
=SUM(IF($A$7:$A$10&$B$7:$B$10=$B$1&$A2,$C$7:$C$10) )
this assumes your data available begins in A7 and product end in B10. For
COST-Harry the formula would be
=SUM(IF($A$7:$A$10&$B$7:$B$10=$C$1&$A2,$C$7:$C$10) )
if you don't see {} around your formula when you are done you didn't hit
CTL+Shift+Enter.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Nimish Shah" wrote:

Dear Sir,

I am in seacrh of a formula. I want to fill up the cell of COST-TOm and
COST-Harry.

Product COST-TOM COST-Harry
Apple ??? ???
Mango ??? ???
Banana ??? ???

Data Available
Farmer Product COST
Tom Apple 10
Harry Apple 20
Harry Banana 7
Tom Apple 10
Harry Mango 30
Harry Apple 20
Tom Mango 15
Harry Mango 30

Regards,
Nimish

Search of a Formula/Function
 

Dear Nimish,

use this formula
=SUMPRODUCT(--($B$11:$B$18=$A5), --($A$11:$A$18="Tom")*$C$11:$C$18)
Change Tom to Harry in column C

"Nimish Shah" wrote:

Dear Sir,

I am in seacrh of a formula. I want to fill up the cell of COST-TOm and
COST-Harry.

Product COST-TOM COST-Harry
Apple ??? ???
Mango ??? ???
Banana ??? ???

Data Available
Farmer Product COST
Tom Apple 10
Harry Apple 20
Harry Banana 7
Tom Apple 10
Harry Mango 30
Harry Apple 20
Tom Mango 15
Harry Mango 30

Regards,
Nimish

Search of a Formula/Function
 

Thank you sir. This works very fine.

Regards,

Nimish

"Excel_Learner" wrote:

Dear Nimish,

use this formula
=SUMPRODUCT(--($B$11:$B$18=$A5), --($A$11:$A$18="Tom")*$C$11:$C$18)
Change Tom to Harry in column C

"Nimish Shah" wrote:

Dear Sir,

I am in seacrh of a formula. I want to fill up the cell of COST-TOm and
COST-Harry.

Product COST-TOM COST-Harry
Apple ??? ???
Mango ??? ???
Banana ??? ???

Data Available
Farmer Product COST
Tom Apple 10
Harry Apple 20
Harry Banana 7
Tom Apple 10
Harry Mango 30
Harry Apple 20
Tom Mango 15
Harry Mango 30

Regards,
Nimish

Search of a Formula/Function
 

Thank you sir. I have solved my problem. I have learnt Pivot Tables and this
is really wonderful and woks faster then typing manually all the formulas.

Regards,

Nimish



"iliace" wrote:

Pivot tables are easier to grasp than formulas, and they are certainly
easier to set up and maintain than a bunch of formulas. Why reinvent
the wheel when Excel already has a built-in feature that does what you
want perfectly?


On Dec 27, 9:56 am, Nimish Shah
wrote:
Hi Fred,

Thank you but i have never worked with Pivot Tables. Is there a formula with
IF, sumproduct etc etc. which can help. In the meantime i am trying to learn
Pivot Table.

Regards,

Nimish

"Fred Smith" wrote:
You want a pivot table. It's perfect for your application. Look here for help:
http://www.cpearson.com/excel/pivots.htm

--
Regards,
Fred

"Nimish Shah" wrote in message
...
Dear Sir,

I am in seacrh of a formula. I want to fill up the cell of COST-TOm and
COST-Harry.

Product COST-TOM COST-Harry
Apple ??? ???
Mango ??? ???
Banana ??? ???

Data Available
Farmer Product COST
Tom Apple 10
Harry Apple 20
Harry Banana 7
Tom Apple 10
Harry Mango 30
Harry Apple 20
Tom Mango 15
Harry Mango 30

Regards,
Nimish

Search of a Formula/Function
 

i want to use formula in excel wheel h and wheel v how can i use it plz tell me
plz tell me meaning as well