I am working with a table in Excel 2010 and I need to be able to show a value in a column that shows the sum of a range for every value that meets two criteria. I also need it to round each value prior to summing. I've seen people use an array formula to round each value independently prior to summing, but I am not seeing how to do this with a sumifs statement.
My formula looks like this:
=sumifs(pData[premium],pdata[Pol],[@[Pol]],pdata[LineNum],[@[LineNum]])
Now.. I don't want to round the sum of everything, I want everything rounded prior to summing. Essentially, for each value that meets the sumif criteria, it should be rounded up or down prior to summing.
=Round(sumifs(pData[premium],pdata[Pol],[@[Pol]],pdata[LineNum],[@[LineNum]]),0)
sums the total and is generally off by a few numbers.
Instead of a SUMIF
, would this work for your situation? As @nutsch pointed out, this can be arranged as follows and it does not need to be entered as an array formula:
=SUMPRODUCT(($A$1:$A$12="A")*($B$1:$B$12="B")*ROUND($C$1:$C$12,0))
This finds the value where column A = "A" and column B = "B", and then returns the rounded number in column C.