sumif comparing columns ???

rollo · Mar 27th, 2002, 08:47 AM

I need to compare one column of numerical data on one worksheet with another column in a different worksheet. Then, where the numbers are the same I need the corresponding numbers from a third column totalling.
Eg
Sheet1..........Sheet2
Col A...........Col A.....Col B
(If the contents of the range of cells in column A of Sheet 1 equal the contents of the range of cells in column A of Sheet 2 - then sum all the corresponding cells in column B of sheet 2)
Any ideas would be greatly appreciated.

Rocky E · Mar 27th, 2002, 09:01 AM

Not quite enough information, but sounds like a CSE or array formula so check out http://www.cpearson.com/excel/array.htm

HTH
Rocky...

Aladin Akyurek · Mar 27th, 2002, 09:46 AM

Quote:

On 2002-03-27 07:47, rollo wrote:
I need to compare one column of numerical data on one worksheet with another column in a different worksheet. Then, where the numbers are the same I need the corresponding numbers from a third column totalling.
Eg
Sheet1..........Sheet2
Col A...........Col A.....Col B
(If the contents of the range of cells in column A of Sheet 1 equal the contents of the range of cells in column A of Sheet 2 - then sum all the corresponding cells in column B of sheet 2)
Any ideas would be greatly appreciated.

Try in Sheet2:

=SUMPRODUCT((Sheet1!A2:A40=A2:A40),B2:B40)

rollo · Mar 27th, 2002, 09:52 AM

Thanks for the post, Rocky E.
Have looked at the link but, while =SUM(IF(A1:A10=B1:B10,1,0)) looks a possibility it only returns the number of cells in one range which are equal to their counterparts in the other range. I need a sum of the actual corresponding values from another column.

rollo · Mar 27th, 2002, 09:53 AM

Oops sorry Aladin - just spotted your post. Will check it out. Many thanks.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 27th, 2002, 08:47 AM	#1
rollo New Member Join Date: Feb 2002 Posts: 43	I need to compare one column of numerical data on one worksheet with another column in a different worksheet. Then, where the numbers are the same I need the corresponding numbers from a third column totalling. Eg Sheet1..........Sheet2 Col A...........Col A.....Col B (If the contents of the range of cells in column A of Sheet 1 equal the contents of the range of cells in column A of Sheet 2 - then sum all the corresponding cells in column B of sheet 2) Any ideas would be greatly appreciated.

Mar 27th, 2002, 09:01 AM	#2
Rocky E Board Regular Join Date: Feb 2002 Location: North Alabama, USA Posts: 105	Not quite enough information, but sounds like a CSE or array formula so check out http://www.cpearson.com/excel/array.htm HTH Rocky...

Mar 27th, 2002, 09:52 AM	#4
rollo New Member Join Date: Feb 2002 Posts: 43	Thanks for the post, Rocky E. Have looked at the link but, while =SUM(IF(A1:A10=B1:B10,1,0)) looks a possibility it only returns the number of cells in one range which are equal to their counterparts in the other range. I need a sum of the actual corresponding values from another column.

Mar 27th, 2002, 09:53 AM	#5
rollo New Member Join Date: Feb 2002 Posts: 43	Oops sorry Aladin - just spotted your post. Will check it out. Many thanks.