Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Pivotrend
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
vezerid
 
Posts: n/a
Default summing values from adjacent column with refrence from adjacent column

=SUMPRODUCT(C1:C8*--(A1:A8="MSFT")*--(B1:B8=26.20))

HTH
Kostis Vezerides

  #3   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
R..VENKATARAMAN
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Pivotrend
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Pivotrend
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
vezerid
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

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
Sum up values from adjacent column Pivotrend Excel Discussion (Misc queries) 4 February 21st 06 12:08 PM
linking values of column A to values of column B os97 Excel Discussion (Misc queries) 2 February 21st 06 12:14 AM
How to get the values using a condition refering to the other column ramana Excel Worksheet Functions 2 October 28th 05 12:54 PM
creating a bar graph Johnfli Excel Discussion (Misc queries) 0 October 26th 05 08:16 PM
match and count words David Excel Worksheet Functions 5 July 4th 05 02:24 AM


All times are GMT +1. The time now is 11:05 AM.

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

About Us

"It's about Microsoft Excel"