A B C D E F
G
name city state #cks return code ck# $amnt
Jones denver co 1 nsf 5725
1000
Jones denver co 1 nsf 5723
1000
Jones denver co 1 nsf 5741
1000
Jones Total 3
3000
Smith Palmer AK 1 nsf 2742
650
Smith Palmer AK 1 nsf 2767
350
Smith Total 2
1000
Johnson Troy MI 1 clsd 6379
1400
Johnson Total 1
1000

The subtotals are the number of checks and total dollar amount. I need to
find the numer of customer that only have 1 check.





"Jacob Skaria" wrote:

Could you please let us know how your data is arranged..
Is there any identifier to know that the row contains subtotals...

--
If this post helps click Yes
---------------
Jacob Skaria


"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.