| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I sort by row instead of by column? | Excel Worksheet Functions | |||
| Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
| How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) | |||
| How can I sort an entire spreadsheet from a list | Excel Worksheet Functions | |||
| Copying the contents of a column into a chart | Excel Worksheet Functions | |||
Linear Mode
