Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Search of a Formula/Function
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 20
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 623
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 20
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 229
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 20
Default 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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 772
Default 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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 20
Default 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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10,593
Default 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


Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 53
Default 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
Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 20
Default 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
Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
i am in search of Formula / Function in EXCEL-2003 Nimish Shah Excel Discussion (Misc queries) 17 January 24th 08 02:50 PM
search function crodriguez1976 New Users to Excel 3 August 17th 06 01:39 PM
Search function andre guerreiro Excel Worksheet Functions 3 August 4th 06 10:31 PM
Search function. the_intern Excel Worksheet Functions 2 August 3rd 06 11:40 AM
SEARCH function jcastellano Excel Worksheet Functions 5 April 3rd 06 03:53 PM


All times are GMT +1. The time now is 11:47 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement