Need Sumif help....

[Dan B]

Here are my formulas:
=SUMIF(D333,"<8",D333)
=SUMIF(D434,">8",D434)

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.

 

[Bernard Liengme]

Firstly, you need only =SUMIF(D333,"<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

 

[Toppers]

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]

You are a better mind reader than I am!

 

[Dan B]

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]

To sum the cell values in excess of 8
=SUMPRODUCT(--(A1:A7>8),(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

 

[Toppers]

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

 

[Toppers]

..... of course! <bg>

To sum the cell values in excess of 8
=SUMPRODUCT(--(A1:A7>8),(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

 

[Dan B]

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

Thanks again,

Dan

 

[Dan B]

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 are:
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.

 

[Toppers]

=SUMPRODUCT((D434<8)*(8-D434)*(D434<>""))

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


and for individual rows:

=ABS(A1-8)

and copy down as required

HTH