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?

Order number	Item number	Optimal Shipping Units	SUMIF
588999072	HASU5029873	1.08	7.230090909
588999072	MRKU4793689	1.05	7.230090909
588999072	MRSU3550666	1.18	7.230090909
588999072	MRSU3790190	0.67	7.230090909
588999072	MSKU9751899	1.18	7.230090909
588999072	SUDU8777697	1.00	7.230090909
588999072	TRLU7130071	1.06	7.230090909
588999073	MRKU3112163	0.35	0.346927273
588999075	HASU4470499	1.05	1.052163636
588999076	MRSU3700874	0.56	0.557036364
588999077	UETU5442250	0.81	0.812490909
588999078	TGHU9689536	1.07	1.072854545
65021489	BMOU5630010	0.33	0.332272727
66688203	FBLU0166735	0.51	0.506781818
60021495	CRXU9840767	0.26	0.264272727
60354858	TGHU9120410	1.62	1.623453555
60354876	GATU8672504	0.54	7.283963636
60354876	HLBU1284890	1.16	7.283963636
60354876	HLBU2275362	1.03	7.283963636
60354876	HLXU6360264	1.00	7.283963636
60354876	TCNU1050819	1.05	7.283963636
60354876	TCNU6697243	1.09	7.283963636
60354876	UACU5758623	1.08	7.283963636
60354876	UACU5945666	0.33	7.283963636

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[Fluff]

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