Search of a Formula/Function

[Nimish Shah]

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

 

[Fred Smith]

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

 

[John Bundy]

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.

 

[Nimish Shah]

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

 

[Bob Phillips]

=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)

 

[iliace]

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?

 

[Nimish Shah]

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

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

 

[Excel_Learner]

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]

Thank you sir. This works very fine.

Regards,

Nimish

 

[Nimish Shah]

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

 

[mandeep verma]

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