| Home |
| Search |
| Today's Posts |
|
#8
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 |
| 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 | |||
Threaded Mode
