Sumif trying to Sum cells with Round Function

M

Mike H.

It appears that in certain circumstances when I do a sumif that includes
fields that contain a rounding function that the rounded numbers are omitted
from the sumif. Has anyone else experienced a similar situation and could
possibly shed light on this issue? Version 2003.
 
P

Peo Sjoblom

I have never seen that, I just tested a dummy range where I rounded
and used SUMIF and I got the result I expected?

--


Regards,


Peo Sjoblom
 
M

Mike H.

I forgot to mention that also some of the data with the round function in it
is filted out of sight. It doesn't always eliminate the data from being in
the sumif but sometimes it does. Not sure I can see a pattern or not.
Thanks.
 
R

RagDyer

Post your "Round" formula.

Something like this would cause you problems:

=ROUND(A1,2)&" LBS."
 
P

Peo Sjoblom

SUMIF will always include hidden or filtered data, you can use
a combination of SUMPRODUCT and SUBTOTAL plus some more function to just
criteria sum visible cells.

--


Regards,


Peo Sjoblom
 
M

Mike H.

I figured out my problem. I had one cell that had a subtotal() function on
it. Then
in another cell I took that subtotal and rounded it. So when I filtered the
row out, the subtotal function would recalc and return 0, thus causing my
round to go to 0 and the sumif to sum to 0 also. Subtotal doesn't include
data if the cell is hidden, which I knew but forgot about checking as the
subtotal was not in the direct number I was sumiffing (is that a word).
Anyway, mystery solved.
 
Top