Sum cell offset by division by criteria

**Foreverlearning** · 02-06-2012, 04:36 AM

Hi,

Not sure if Title reflects my question

I have for example a column with values say A1:A10 and also another column say C1:C10 with some values. What I want to work out is sum the result of
values from these.

Column A Column C
1.57 120
2.45
1.45 245
1.50
1.57
Sum result here xxxx
What to sum - 1.57/120 and 1.45/245

There maybe 20 rows of data and column c will not have data for each row.

Does this makes sense ?

Thanks
Lionel

**Fotis1991** · 02-06-2012, 04:46 AM

Hi

Maybe somrthing like this??

In C1>>>>=IF(B2>0,A2/B2,0)

Copy down.

Then use >>>=SUM(C1:C20

Hope to helps you.

**Foreverlearning** · 02-06-2012, 05:08 AM

Hi Fotis,

Actually the C column must be for entering values not formulas

The only place for formulas can only be in the sum result area

Thanks
Lionel

**Fotis1991** · 02-06-2012, 05:30 AM

Lionel

The idea is, a helper(hide if you like) Column....

Take a look to the example.

**Foreverlearning** · 02-06-2012, 05:50 AM

Hi Fotis,

I appreciate the help but the number of columns are 20 plus and dynamic and makes it more difficult
I would think that there must be a formula to solve this.

Column A is static but the other columns reference the static column A

Lionel

**Chandrajit** · 02-06-2012, 09:05 AM

HI
may be the formula will work for you

=SUM(IF(NOT(ISBLANK(B1:B5)),A1:A5/B1:B5))

I have attached the file used earlier by Fotis1991

and the cell with the formula is highlighted in yellow. You will have to Array Enter the formula by hitting Ctrl + Shift + Enter.

PLease let me know if that solves your query!

**Haseeb A** · 02-06-2012, 03:52 PM

Since you are on Excel 2007, this should work

=SUM(IFERROR(A1:A10/C1:C10,0))

with CTRL+SHIFT+ENTER, rather than just ENTER

**Foreverlearning** · 02-07-2012, 03:44 AM

Hi Chandrajit & Haseeb A,

Thank you both for your solutions, both work well.

A question before closing this thread

Will Array formulas dramatically slow down the spreadsheet if use frequently over say 20 week-sheets in same work book
and over say 30 rows and columns per sheet

Thanks
Lionel

**Chandrajit** · 02-07-2012, 10:11 AM

Dear Friend,
I do not think that it will cause more slowdown compared to similar usage with non-array formulae. Still better - compact formulae will tremendously reduce the load on worksheets.
As you can see Haseeb A's solution is more compact and will involve much lesser calculations by Excel than in my solution.
You may please mark the thread as solved if your query is solved.
----------------------
PS. Good one Haseeb