SUM IF - Round up If - Round Down Else

[lager1001]

Hi all,

I need help building out a formula here.

If I have a column of data that I am performing a SUMIF on, I would like to incorporate a Rounding calculation to the SUMIF formula. If the sum of the data's decimal point is below .5, round down nearest whole number, if over .5 round up to nearest whole number. See below example. The first 7 lines associated with order number 588999072 should round down to 7. The line associated with order number 60354858 should round up to 2. Any ideas?

[TABLE="width: 527"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Order number[/TD]
[TD]Item number[/TD]
[TD]Optimal Shipping Units[/TD]
[TD]SUMIF[/TD]
[/TR]
[TR]
[TD]588999072[/TD]
[TD]HASU5029873[/TD]
[TD]1.08[/TD]
[TD="align: right"]7.230090909[/TD]
[/TR]
[TR]
[TD]588999072[/TD]
[TD]MRKU4793689[/TD]
[TD]1.05[/TD]
[TD="align: right"]7.230090909[/TD]
[/TR]
[TR]
[TD]588999072[/TD]
[TD]MRSU3550666[/TD]
[TD]1.18[/TD]
[TD="align: right"]7.230090909[/TD]
[/TR]
[TR]
[TD]588999072[/TD]
[TD]MRSU3790190[/TD]
[TD]0.67[/TD]
[TD="align: right"]7.230090909[/TD]
[/TR]
[TR]
[TD]588999072[/TD]
[TD]MSKU9751899[/TD]
[TD]1.18[/TD]
[TD="align: right"]7.230090909[/TD]
[/TR]
[TR]
[TD]588999072[/TD]
[TD]SUDU8777697[/TD]
[TD]1.00[/TD]
[TD="align: right"]7.230090909[/TD]
[/TR]
[TR]
[TD]588999072[/TD]
[TD]TRLU7130071[/TD]
[TD]1.06[/TD]
[TD="align: right"]7.230090909[/TD]
[/TR]
[TR]
[TD]588999073[/TD]
[TD]MRKU3112163[/TD]
[TD]0.35[/TD]
[TD="align: right"]0.346927273[/TD]
[/TR]
[TR]
[TD]588999075[/TD]
[TD]HASU4470499[/TD]
[TD]1.05[/TD]
[TD="align: right"]1.052163636[/TD]
[/TR]
[TR]
[TD]588999076[/TD]
[TD]MRSU3700874[/TD]
[TD]0.56[/TD]
[TD="align: right"]0.557036364[/TD]
[/TR]
[TR]
[TD]588999077[/TD]
[TD]UETU5442250[/TD]
[TD]0.81[/TD]
[TD="align: right"]0.812490909[/TD]
[/TR]
[TR]
[TD]588999078[/TD]
[TD]TGHU9689536[/TD]
[TD]1.07[/TD]
[TD="align: right"]1.072854545[/TD]
[/TR]
[TR]
[TD]65021489[/TD]
[TD]BMOU5630010[/TD]
[TD]0.33[/TD]
[TD="align: right"]0.332272727[/TD]
[/TR]
[TR]
[TD]66688203[/TD]
[TD]FBLU0166735[/TD]
[TD]0.51[/TD]
[TD="align: right"]0.506781818[/TD]
[/TR]
[TR]
[TD]60021495[/TD]
[TD]CRXU9840767[/TD]
[TD]0.26[/TD]
[TD="align: right"]0.264272727[/TD]
[/TR]
[TR]
[TD]60354858[/TD]
[TD]TGHU9120410[/TD]
[TD]1.62[/TD]
[TD="align: right"]1.623453555[/TD]
[/TR]
[TR]
[TD]60354876[/TD]
[TD]GATU8672504[/TD]
[TD]0.54[/TD]
[TD="align: right"]7.283963636[/TD]
[/TR]
[TR]
[TD]60354876[/TD]
[TD]HLBU1284890[/TD]
[TD]1.16[/TD]
[TD="align: right"]7.283963636[/TD]
[/TR]
[TR]
[TD]60354876[/TD]
[TD]HLBU2275362[/TD]
[TD]1.03[/TD]
[TD="align: right"]7.283963636[/TD]
[/TR]
[TR]
[TD]60354876[/TD]
[TD]HLXU6360264[/TD]
[TD]1.00[/TD]
[TD="align: right"]7.283963636[/TD]
[/TR]
[TR]
[TD]60354876[/TD]
[TD]TCNU1050819[/TD]
[TD]1.05[/TD]
[TD="align: right"]7.283963636[/TD]
[/TR]
[TR]
[TD]60354876[/TD]
[TD]TCNU6697243[/TD]
[TD]1.09[/TD]
[TD="align: right"]7.283963636[/TD]
[/TR]
[TR]
[TD]60354876[/TD]
[TD]UACU5758623[/TD]
[TD]1.08[/TD]
[TD="align: right"]7.283963636[/TD]
[/TR]
[TR]
[TD]60354876[/TD]
[TD]UACU5945666[/TD]
[TD]0.33[/TD]
[TD="align: right"]7.283963636[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about
=ROUND(SUMIF(A:A,A2,C:C),0)