Need Sumif help....
 

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.

Need Sumif help....
 

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.

Need Sumif help....
 

first:
=COUNTIF($D$2:$D$10,"<8")*8-SUMIF($D$2:$D$10,"<8")
Second:
=SUMIF($D$2:$D$10,"8")-COUNTIF($D$2:$D$10,"8")*8

HTH

"Bernard Liengme" wrote:

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.

Need Sumif help....
 

You are a better mind reader than I am!
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Toppers" wrote in message
...
first:
=COUNTIF($D$2:$D$10,"<8")*8-SUMIF($D$2:$D$10,"<8")
Second:
=SUMIF($D$2:$D$10,"8")-COUNTIF($D$2:$D$10,"8")*8

HTH

"Bernard Liengme" wrote:

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.

Need Sumif help....
 

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.

Need Sumif help....
 

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.

Need Sumif help....
 

Bernard,
After my "mind reading" trick, <g, is there a 'better'
solution than the one I offered?

"Bernard Liengme" wrote:

You are a better mind reader than I am!
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Toppers" wrote in message
...
first:
=COUNTIF($D$2:$D$10,"<8")*8-SUMIF($D$2:$D$10,"<8")
Second:
=SUMIF($D$2:$D$10,"8")-COUNTIF($D$2:$D$10,"8")*8

HTH

"Bernard Liengme" wrote:

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.

Need Sumif help....
 

..... of course! <bg

"Bernard Liengme" wrote:

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.

Need Sumif help....
 

Thank you both. Sorry it wasn't clear...I was rushing too fast.

Thanks again,

Dan


"Toppers" wrote in message
...
.... of course! <bg

"Bernard Liengme" wrote:

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.

Need Sumif help....
 

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.

Need Sumif help....
 

=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.