Search of a Formula/Function

  • Thread starter Thread starter Nimish Shah
  • Start date Start date
N

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

***************************
 
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
 
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
 
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
 
Back
Top