On Apr 30, 11:40*am, katagrga
wrote:
I am trying to get a count of the subtotaled cells that have value of "1".. *I
have searched through previous posts and have not been able to get any of the
formulas to return the correct value. *The formulas I have tried are and the
returns I received are as follows:

=SUMPRODUCT(--(D2:D6303="1"),SUBTOTAL(3,OFFSET(D2,ROW(D2:D6303)-ROW(D2),))) *

=SUMPRODUCT(SUBTOTAL(3,OFFSET(D2:D6303,ROW(D2:D630 3)-MIN(ROW(D2:D6303)),,1)*),--(D2:D6303="1"))

both returns a 0. *without the "' returns 4991 which is the number of
subtotals.

=COUNTIF($D$2:$D$6303,"1") returns 5115

I've tried several others as well. *Any help is greatly appreciated.

Your countif method is the proper way to do this. and although you
don't need the 1 to be in quotation marks for this purpose, it should
still work. Your range is 6302 cells long, but you said you only have
4991 subtotals. Could it be that you are checking the wrong range, or
are some of the cells in the range blank?

Is there some rounding of decimal places in your range? A 1.2 and a .
97 will both look like a 1 in the cell if you are not showing decimal
places, but the calculation won't see them as a 1. If this is the
case you could modify your formula to be:

=countif($D$2:$D$6303,"<2")

Or you could try "<1.01" etc.

Hope this helps,
TK