Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
summing values from adjacent column with refrence from adjacent column
sup pros i have this info A1: MSFT A2: CAT A3: MSFT A4: IBM A5: MSFT A6: MSFT A7: CSCO A8: MSFT A9: MSFT & B1: 26.20 B2: 76.00 B3: 26.21 B4: 81.45 B5: 26.20 B6: 26.19 B7: 18.20 B8: 26.20 B9: 26.23 & C1: 23,670 C2: 76.00 C3: 20,000 C4: 81.45 C5: 25,998 C6: 34,005 C7: 18.20 C8: 35,000 C9: 17,050 I want to sum up the values in column C if only at this condition , the adjacent column (B) is at 26.20 & column (A) is MSFT the result would be C1 , C5 , & C8 = 23,670+25,998+35,000 = 84,668 how do i do that ? -- Pivotrend ------------------------------------------------------------------------ Pivotrend's Profile: http://www.excelforum.com/member.php...fo&userid=4062 View this thread: http://www.excelforum.com/showthread...hreadid=518953 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
summing values from adjacent column with refrence from adjacent column
=SUMPRODUCT(C1:C8*--(A1:A8="MSFT")*--(B1:B8=26.20))
HTH Kostis Vezerides |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
summing values from adjacent column with refrence from adjacent column
Kostis,
You do not need * and --, one or the other, they do the same thing =SUMPRODUCT(C1:C8,--(A1:A8="MSFT"),--(B1:B8=26.20)) or =SUMPRODUCT(C1:C8*(A1:A8="MSFT")*(B1:B8=26.20)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "vezerid" wrote in message oups.com... =SUMPRODUCT(C1:C8*--(A1:A8="MSFT")*--(B1:B8=26.20)) HTH Kostis Vezerides |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
summing values from adjacent column with refrence from adjacent column
in an empty cell type
=SUMPRODUCT((A1:A9="MSFT")*(B1:B9=26.2)*(C1:C9)) "Pivotrend" wrote in message ... sup pros i have this info A1: MSFT A2: CAT A3: MSFT A4: IBM A5: MSFT A6: MSFT A7: CSCO A8: MSFT A9: MSFT & B1: 26.20 B2: 76.00 B3: 26.21 B4: 81.45 B5: 26.20 B6: 26.19 B7: 18.20 B8: 26.20 B9: 26.23 & C1: 23,670 C2: 76.00 C3: 20,000 C4: 81.45 C5: 25,998 C6: 34,005 C7: 18.20 C8: 35,000 C9: 17,050 I want to sum up the values in column C if only at this condition , the adjacent column (B) is at 26.20 & column (A) is MSFT the result would be C1 , C5 , & C8 = 23,670+25,998+35,000 = 84,668 how do i do that ? -- Pivotrend ------------------------------------------------------------------------ Pivotrend's Profile: http://www.excelforum.com/member.php...fo&userid=4062 View this thread: http://www.excelforum.com/showthread...hreadid=518953 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
summing values from adjacent column with refrence from adjacent column
worked fine vezerid thank you -- Pivotrend ------------------------------------------------------------------------ Pivotrend's Profile: http://www.excelforum.com/member.php...fo&userid=4062 View this thread: http://www.excelforum.com/showthread...hreadid=518953 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
summing values from adjacent column with refrence from adjacent column
Bob Phillips -- Pivotrend ------------------------------------------------------------------------ Pivotrend's Profile: http://www.excelforum.com/member.php...fo&userid=4062 View this thread: http://www.excelforum.com/showthread...hreadid=518953 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
summing values from adjacent column with refrence from adjacent column
Bob,
thanks for the tip. I guess it is a remainder from older days, when I was first trying similar formulas, either with SUMPRODUCT or with SUM(IF... Having not thought of the -- conversion before joining the newsgroups, I sometimes had to devise more elaborate conditions. Thus some insecurity often prompts me to overdo it with conversions lol. With this opportunity I must say that I have learned a lot of things from your posts and continue learning. Keep up the good work! Kostis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum up values from adjacent column | Excel Discussion (Misc queries) | |||
linking values of column A to values of column B | Excel Discussion (Misc queries) | |||
How to get the values using a condition refering to the other column | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
match and count words | Excel Worksheet Functions |