Column additions query
 

I have 2 columns of numbers.

There are no 'zeros' in Col 1 - all are greater than zero.

Some of the numbers in Col 2 are zero but most are greater than zero.

I want to be able to add all the numbers in column #2 that are greater than
zero and then divide that number by all the numbers in Column #1 that
coincide only with those numbers in Col 2 that are greater than zero.

In other words I do not want to include any numbers in the Col 1 addition
that are on the same row as the 'zeros' in Col 2.

regards

PeterH

You could use two SumIf's. Something similar to:

=SUMIF(I8:I12,"0",I8:I12)/SUMIF(I8:I12,"0",H8:H12)

--
Damon Longworth

Don't miss out on the 2005 Excel User Conference
www.ExcelUserConference.com


"My View" <reply to wrote in message
...
I have 2 columns of numbers.

There are no 'zeros' in Col 1 - all are greater than zero.

Some of the numbers in Col 2 are zero but most are greater than zero.

I want to be able to add all the numbers in column #2 that are greater
than
zero and then divide that number by all the numbers in Column #1 that
coincide only with those numbers in Col 2 that are greater than zero.

In other words I do not want to include any numbers in the Col 1 addition
that are on the same row as the 'zeros' in Col 2.

regards

PeterH

Thank-you - that seems have worked perfectly.
regards
PeterH


"Damon Longworth" wrote in message
...
You could use two SumIf's. Something similar to:

=SUMIF(I8:I12,"0",I8:I12)/SUMIF(I8:I12,"0",H8:H12)

--
Damon Longworth

Don't miss out on the 2005 Excel User Conference
www.ExcelUserConference.com


"My View" <reply to wrote in message
...
I have 2 columns of numbers.

There are no 'zeros' in Col 1 - all are greater than zero.

Some of the numbers in Col 2 are zero but most are greater than zero.

I want to be able to add all the numbers in column #2 that are greater
than
zero and then divide that number by all the numbers in Column #1 that
coincide only with those numbers in Col 2 that are greater than zero.

In other words I do not want to include any numbers in the Col 1
addition
that are on the same row as the 'zeros' in Col 2.

regards

PeterH

Assuming there are no negatives in either column,

=SUM(B1:B100)/SUMIF(B1:B100,"0",A1:A100)

On Sat, 25 Jun 2005 11:34:47 GMT, "My View" <reply to
wrote:

I have 2 columns of numbers.

There are no 'zeros' in Col 1 - all are greater than zero.

Some of the numbers in Col 2 are zero but most are greater than zero.

I want to be able to add all the numbers in column #2 that are greater than
zero and then divide that number by all the numbers in Column #1 that
coincide only with those numbers in Col 2 that are greater than zero.

In other words I do not want to include any numbers in the Col 1 addition
that are on the same row as the 'zeros' in Col 2.

regards

PeterH