Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count zero values before specific date | Excel Worksheet Functions | |||
Count unique values among duplicates in a subtotal range | Excel Discussion (Misc queries) | |||
Count the number of specific values in a cell | Excel Worksheet Functions | |||
Trying to count specific values | Excel Worksheet Functions | |||
How do I count my data that are between specific values? | Excel Worksheet Functions |