Column additions query

  • Thread starter Thread starter My View
  • Start date Start date
M

My View

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)
 
Assuming there are no negatives in either column,

=SUM(B1:B100)/SUMIF(B1:B100,">0",A1:A100)
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sumif, multiple criteria 8
sum last 3 numbers greater that 0 in a range 0
Number of rows over 30 days after creation 1
Sumproduct...Average 2
COUNTIF or SUMPRODUCT 1
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Numbers from first column 5
Sum Total with minus in different cell 1

Back
Top