=SUMPRODUCT((D4:D34<8)*(8-D4:D34)*(D4:D34<""))

.... to allow for blank cells (treated as 0)


and for individual rows:

=ABS(A1-8)

and copy down as required

HTH

"Dan B" wrote:

OK...the people asking for this info have changed their mind somewhat...now
may they need it by row too...so I will need to know if A1 is greater then
8, then by how much, and if its less then 8 then by how much? So if A1 was
10 the result of the first formula would be 2. If A1 was a 7 the result
would be 1.

Also, on your formulas below, the first one, in excess of 8 worked fine, but
the other didn't.

The number in my range from D4 to D34 a
7
7
8
9
9
9
9
9
8
8
8
8
8
8
8
8
7
7
7
7
7
8
8
so summing cells that are less then 8 should have resulted in 7 (7cells that
were 1 less then 8). Your formula gave me 71.

Any idea why?

Thank you so much.



"Bernard Liengme" wrote in message
...
To sum the cell values in excess of 8
=SUMPRODUCT(--(A1:A78),(A1:A7-8))
To sum the amounts by which the cells are less than 8
=SUMPRODUCT(--(A1:A7<8),(8-A1:A7))
See
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dan B" wrote in message
...
The nine would be from summing the difference between 5 and 8 in the
three cells that contain 5. So, I only want to add up the difference
between 8 and the cell value. Maybe this will help clarify....this is to
add up hours that were worked over 8 and under 8. So, if an employee is
expected to work 8 hours a day, and some days they work more, some days
less, I just want to know how many hours more or less then 8 were worked,
and not include the expected 8 hours in the sum.
Does that make sense?

Thanks


"Bernard Liengme" wrote in message
...
Firstly, you need only =SUMIF(D3:D33,"<8"). The third argument is for
cases where you test one range but sum another.

You state "I need to edit these to only sum the amounts that are over or
under 8" but that is exactly what these formulas do.

Can you explain further: "If there were 3 cells in this range that had
the value of 5, then the result of the formula should show 9" Where does
9 come from?

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dan B" wrote in message
...
Here are my formulas:
=SUMIF(D3:D33,"<8",D3:D33)
=SUMIF(D4:D34,"8",D4:D34)

I need to edit these to only sum the amounts that are over or under 8.
For example, for the first formula, if there were 3 cells in this range
that had the value of 5, then the result of the formula should show 9.
For the second formula...;if there were 2 cells with the valud of 10
the result would show 4.

I think I need to use MIN in there somewhere, but I'm not sure how.

Thanks.