On Apr 30, 3:16*pm, 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- Hide quoted text -

- Show quoted text -

Please ignore cell references in the 2nd formula, they were used for
my test run. Use the first example.

TK