That did it! Thank you so much.

"Cortez" wrote:

On Apr 30, 1:34 pm, katagrga
wrote:
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..- Hide quoted text -

- Show quoted text -

This should do it. Remember to control+shift+enter to add this
formula:

=SUM((RIGHT(A$2:A$6303,5)="total")*IF((D$2:D$6303) =1,1,0))

Excel will surround the formula with brackets after the control+shift
+enter to look like {=SUM((RIGHT(A$28:A$35,5)="total")*IF((D$28:D$35)
=1,1,0))}

Let me know if this works,
TK