count specific subtotal values only

[katagrga]

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(--(D26303="1"),SUBTOTAL(3,OFFSET(D2,ROW(D26303)-ROW(D2),)))



=SUMPRODUCT(SUBTOTAL(3,OFFSET(D26303,ROW(D26303)-MIN(ROW(D26303)),,1)),--(D26303>="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.

 

[Jacob Skaria]

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]

Oops, Sorry the below is a wrong post...

 

[Jacob Skaria]

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

 

[Cortez]

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 ofthe
formulas to return the correct value.  The formulas I have tried are and the
returns I received are as follows:

=SUMPRODUCT(--(D26303="1"),SUBTOTAL(3,OFFSET(D2,ROW(D26303)-ROW(D2),)))  

=SUMPRODUCT(SUBTOTAL(3,OFFSET(D26303,ROW(D26303)-MIN(ROW(D26303)),,1)­),--(D26303>="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

 

[Sri]

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

check the article below,
http://funwithexcel.blogspot.com/2009/04/sum-particular-rows-in-column-using.html

 

[katagrga]

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.

 

[Cortez]

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 needto
find the numer of customer that only have 1 check.






- 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$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$35)
=1,1,0))}

Let me know if this works,
TK

 

[Cortez]

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

=SUM((RIGHT(A$2:A$6303,5)="total")*IF((D$2$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$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

 

[katagrga]

That did it! Thank you so much.

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

=SUM((RIGHT(A$2:A$6303,5)="total")*IF((D$2$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$35)
=1,1,0))}

Let me know if this works,
TK