Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic subtotals and eliminate dupes
Working with a large spreadsheet. I will engage Autofilter and want to add a
section at the bottom for subtotals pertaining to the filtered data shown. What formula(s) would I use for that since it would change for each filter selection? Also, I want to put in a dynamic count for an array, however there will be duplicates. How do I eliminate a count with dupes in an array? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic subtotals and eliminate dupes
If you turn on the filter and make the filter active (that is, make a
selection from one of the filtered columns), the autosum button will use the subtotal function rather than sum. If you want to enter it yourself, the formula is =subtotal(9,range), where range is the entire (unfiltered) range; subtotal will exclude cells that get filtered out by your autofilter selections. "ttbbgg" wrote: Working with a large spreadsheet. I will engage Autofilter and want to add a section at the bottom for subtotals pertaining to the filtered data shown. What formula(s) would I use for that since it would change for each filter selection? Also, I want to put in a dynamic count for an array, however there will be duplicates. How do I eliminate a count with dupes in an array? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic subtotals and eliminate dupes
For a unique count on filtered data, try...
=COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),,1)),A 2:A10),IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),,1)),A2:A10))) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , ttbbgg wrote: Also, I want to put in a dynamic count for an array, however there will be duplicates. How do I eliminate a count with dupes in an array? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic subtotals and eliminate dupes
OK, no more error, but I am getting a count of 0. Help!
"Domenic" wrote: For a unique count on filtered data, try... =COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),,1)),A 2:A10),IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),,1)),A2:A10))) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , ttbbgg wrote: Also, I want to put in a dynamic count for an array, however there will be duplicates. How do I eliminate a count with dupes in an array? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic subtotals and eliminate dupes
Sorry, this isn't working. I keep getting a formula error. I am seeing
array references throughout. Do I simply change the array reference and keep it consistent for every entry? Please note that my row count is 2 through to 15042. "Domenic" wrote: For a unique count on filtered data, try... =COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),,1)),A 2:A10),IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),,1)),A2:A10))) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , ttbbgg wrote: Also, I want to put in a dynamic count for an array, however there will be duplicates. How do I eliminate a count with dupes in an array? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic subtotals and eliminate dupes
In article ,
ttbbgg wrote: OK, no more error, but I am getting a count of 0. Help! Make sure that you confirm the formula with CONTROL+SHIFT+ENTER, instead of just ENTER. In other words, type the formula, press the CONTROL and SHIFT keys down, while these two keys are pressed down, press ENTER. Excel will place braces {} around the formula, indicating that you've entered the formula correctly. Hope this helps! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic subtotals and eliminate dupes
I think I am getting a value of 0 because FREQUENCY only recognizes numbers
and not text. I have text strings that I need to count for unique entry. "ttbbgg" wrote: OK, no more error, but I am getting a count of 0. Help! "Domenic" wrote: For a unique count on filtered data, try... =COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),,1)),A 2:A10),IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),,1)),A2:A10))) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , ttbbgg wrote: Also, I want to put in a dynamic count for an array, however there will be duplicates. How do I eliminate a count with dupes in an array? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic subtotals and eliminate dupes
In that case, try...
=COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),0,1)), MATCH(A2:A10,A2:A10,0)),ROW(A2:A10)-ROW(A2)+1)) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , ttbbgg wrote: I think I am getting a value of 0 because FREQUENCY only recognizes numbers and not text. I have text strings that I need to count for unique entry. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|