count specific subtotal values only
 

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.

count specific subtotal values only
 

From Office Button--Excel Options--
From Options Window select Advanced Options
Uncheck the first option "After pressing Enter, move selection.


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.

count specific subtotal values only
 

Oops, Sorry the below is a wrong post...
--
If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

From Office Button--Excel Options--
From Options Window select Advanced Options
Uncheck the first option "After pressing Enter, move selection.


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.

count specific subtotal values only
 

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.

count specific subtotal values only
 

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

count specific subtotal values only
 

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.

count specific subtotal values only
 

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

count specific subtotal values only
 

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

count specific subtotal values only
 

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