| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Need help in calculation at specific location
A B C 1 breakfast $13.50 2 water $1.10 3 food $80.00 4 others $13.90 5 food $4 Is it possible to type an equation whereby it will scan A1:C5 and if the word "food" is found it will add the number next to it? I can't seems to use IF(logic_test, true value,false value) for this... pls help -- Lewis Koh ------------------------------------------------------------------------ Lewis Koh's Profile: http://www.excelforum.com/member.php...o&userid=25712 View this thread: http://www.excelforum.com/showthread...hreadid=391279 |
|
#2
|
|||
|
|||
|
=sumif(A:A,"food",B:B)
-- HTH RP (remove nothere from the email address if mailing direct) "Lewis Koh" wrote in message ... A B C 1 breakfast $13.50 2 water $1.10 3 food $80.00 4 others $13.90 5 food $4 Is it possible to type an equation whereby it will scan A1:C5 and if the word "food" is found it will add the number next to it? I can't seems to use IF(logic_test, true value,false value) for this... pls help -- Lewis Koh ------------------------------------------------------------------------ Lewis Koh's Profile: http://www.excelforum.com/member.php...o&userid=25712 View this thread: http://www.excelforum.com/showthread...hreadid=391279 |
|
#3
|
|||
|
|||
|
hi,
=sumif(B1:B5,"Food",C1:C5) regards FSt1 "Lewis Koh" wrote: A B C 1 breakfast $13.50 2 water $1.10 3 food $80.00 4 others $13.90 5 food $4 Is it possible to type an equation whereby it will scan A1:C5 and if the word "food" is found it will add the number next to it? I can't seems to use IF(logic_test, true value,false value) for this... pls help -- Lewis Koh ------------------------------------------------------------------------ Lewis Koh's Profile: http://www.excelforum.com/member.php...o&userid=25712 View this thread: http://www.excelforum.com/showthread...hreadid=391279 |
|
#4
|
|||
|
|||
|
Thanks guys!!!! It works!!! BTW, how did you guys learn to use these syntax? I can't seems to find the syntax to use from the Excel Help...Don't even know where to start.. -- Lewis Koh ------------------------------------------------------------------------ Lewis Koh's Profile: http://www.excelforum.com/member.php...o&userid=25712 View this thread: http://www.excelforum.com/showthread...hreadid=391279 |
|
#5
|
|||
|
|||
|
Lewis Koh Wrote: A B C D E 1 breakfast $13.50 food $5 2 water $1.10 Shirt $10 3 food $80.00 4 others $13.90 5 food $4 Is it possible to type an equation whereby it will scan A1:C5 and if the word "food" is found it will add the number next to it? I can't seems to use IF(logic_test, true value,false value) for this... pls help Hmm...=sumif(B1:B5,"Food",C1:C5) can't work if food appears on another column. Is there a way to work around this? -- Lewis Koh ------------------------------------------------------------------------ Lewis Koh's Profile: http://www.excelforum.com/member.php...o&userid=25712 View this thread: http://www.excelforum.com/showthread...hreadid=391279 |
|
#6
|
|||
|
|||
|
You have to add a separate summation for that column.
-- HTH RP (remove nothere from the email address if mailing direct) "Lewis Koh" wrote in message ... Lewis Koh Wrote: A B C D E 1 breakfast $13.50 food $5 2 water $1.10 Shirt $10 3 food $80.00 4 others $13.90 5 food $4 Is it possible to type an equation whereby it will scan A1:C5 and if the word "food" is found it will add the number next to it? I can't seems to use IF(logic_test, true value,false value) for this... pls help Hmm...=sumif(B1:B5,"Food",C1:C5) can't work if food appears on another column. Is there a way to work around this? -- Lewis Koh ------------------------------------------------------------------------ Lewis Koh's Profile: http://www.excelforum.com/member.php...o&userid=25712 View this thread: http://www.excelforum.com/showthread...hreadid=391279 |
|
#7
|
|||
|
|||
|
Lewis Koh Wrote: A B C 1 Breakfast $13.50 2 water $1.10 3 Lunch $80.00 4 others $13.90 5 Dinner $4 Hmm...now I wanted to catogorized breakfast,lunch and dinner as food. I tried B6=SUMIF(B1:B5,OR("breakfast","Lunch","Dinner"),C1 :C5). The logic seems to be allowable but it couldn't add up to the correct amount. It always shows "0". May I know if I have typed something wrong? -- Lewis Koh ------------------------------------------------------------------------ Lewis Koh's Profile: http://www.excelforum.com/member.php...o&userid=25712 View this thread: http://www.excelforum.com/showthread...hreadid=391279 |
|
#8
|
|||
|
|||
|
Try instead, array-entered (i.e. press CTRL+SHIFT+ENTER):
=SUM(IF(A1:A5={"Breakfast","Lunch","Dinner"},B1:B5 )) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Lewis Koh" wrote in message ... Lewis Koh Wrote: A B C 1 Breakfast $13.50 2 water $1.10 3 Lunch $80.00 4 others $13.90 5 Dinner $4 Hmm...now I wanted to catogorized breakfast,lunch and dinner as food. I tried B6=SUMIF(B1:B5,OR("breakfast","Lunch","Dinner"),C1 :C5). The logic seems to be allowable but it couldn't add up to the correct amount. It always shows "0". May I know if I have typed something wrong? -- Lewis Koh ------------------------------------------------------------------------ Lewis Koh's Profile: http://www.excelforum.com/member.php...o&userid=25712 View this thread: http://www.excelforum.com/showthread...hreadid=391279 |
|
#9
|
|||
|
|||
|
Try. =SUMPRODUCT(--(ISNUMBER(MATCH(B1:B5,{"Breakfast","Lunch","Dinner "},0))),C1:C5) OR =SUMPRODUCT(--(ISNUMBER(MATCH(B1:B5,E1:E3,0))),C1:C5) ...wherre E1:E3 contains Breakfast, Lunch, and Dinner. Hope this helps! Lewis Koh Wrote: Hmm...now I wanted to catogorized breakfast,lunch and dinner as food. I tried B6=SUMIF(B1:B5,OR("breakfast","Lunch","Dinner"),C1 :C5). The logic seems to be allowable but it couldn't add up to the correct amount. It always shows "0". May I know if I have typed something wrong? -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=391279 |
|
#10
|
|||
|
|||
|
=SUM(SUMIF(B1:B5,{"Breakfast","Lunch","Dinner"},C1 :C5))
-- HTH RP (remove nothere from the email address if mailing direct) "Lewis Koh" wrote in message ... Lewis Koh Wrote: A B C 1 Breakfast $13.50 2 water $1.10 3 Lunch $80.00 4 others $13.90 5 Dinner $4 Hmm...now I wanted to catogorized breakfast,lunch and dinner as food. I tried B6=SUMIF(B1:B5,OR("breakfast","Lunch","Dinner"),C1 :C5). The logic seems to be allowable but it couldn't add up to the correct amount. It always shows "0". May I know if I have typed something wrong? -- Lewis Koh ------------------------------------------------------------------------ Lewis Koh's Profile: http://www.excelforum.com/member.php...o&userid=25712 View this thread: http://www.excelforum.com/showthread...hreadid=391279 |
|
#11
|
|||
|
|||
|
Thanks guys!!! It works!! :) -- Lewis Koh ------------------------------------------------------------------------ Lewis Koh's Profile: http://www.excelforum.com/member.php...o&userid=25712 View this thread: http://www.excelforum.com/showthread...hreadid=391279 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Stock Location Sorting Problem | Excel Discussion (Misc queries) | |||
| How do I color specific data series based on location on data she | Charts and Charting in Excel | |||
| how do I draw a line in a chart at a specific location | Charts and Charting in Excel | |||
| hyperlink an excel cell to a specific location wthin application f | New Users to Excel | |||
| hyperlink an excel cell to a specific location wthin application f | Excel Discussion (Misc queries) | |||
Linear Mode
