| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
use a function for filtered data
I am trying to count items in a spreadsheet using the following formula
=SUM(IF(FREQUENCY(A3:A83,A3:A83)0,1)) when I filter the amount comes up the same--how can I incorporate the subtotal function in here to get a proper number. The data in the range is week numbers that correspond with activities performed each day. I need to filter how many of the weeks the activity is done. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use a function for filtered data
Hi,
First of all, we must thank Biff for sharing this solution. This is the formula to count the unique values in a filtered range. =COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A3:A83)-ROW(A2),)),MATCH(A3:A83,A3:A83,0)),ROW(A3:A83)-ROW(A2))) If this works for you, the kindly post back and thank Biff for the same. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "debl" wrote in message ... I am trying to count items in a spreadsheet using the following formula =SUM(IF(FREQUENCY(A3:A83,A3:A83)0,1)) when I filter the amount comes up the same--how can I incorporate the subtotal function in here to get a proper number. The data in the range is week numbers that correspond with activities performed each day. I need to filter how many of the weeks the activity is done. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use a function for filtered data
=SUM(IF(FREQUENCY(A3:A83,A3:A83)0,1))
That formula will count unique numbers. Try this array formula** : =SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(A3,ROW(A3:A 83)-ROW(A3),)),A3:A83),A3:A83),1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "debl" wrote in message ... I am trying to count items in a spreadsheet using the following formula =SUM(IF(FREQUENCY(A3:A83,A3:A83)0,1)) when I filter the amount comes up the same--how can I incorporate the subtotal function in here to get a proper number. The data in the range is week numbers that correspond with activities performed each day. I need to filter how many of the weeks the activity is done. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use a function for filtered data
This is the formula to count the unique
values in a filtered range. That is a generic formula that will count *both* text and numbers. =SUM(IF(FREQUENCY(A3:A83,A3:A83)0,1)) The OP posted that formula so I'm assuming they want to count the unique numbers in the filtered list. If the range might contain both text and numbers then you need to write the formula to specifically count the numbers only. If the range will only contain numbers then the generic formula should work. -- Biff Microsoft Excel MVP "Ashish Mathur" wrote in message ... Hi, First of all, we must thank Biff for sharing this solution. This is the formula to count the unique values in a filtered range. =COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A3:A83)-ROW(A2),)),MATCH(A3:A83,A3:A83,0)),ROW(A3:A83)-ROW(A2))) If this works for you, the kindly post back and thank Biff for the same. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "debl" wrote in message ... I am trying to count items in a spreadsheet using the following formula =SUM(IF(FREQUENCY(A3:A83,A3:A83)0,1)) when I filter the amount comes up the same--how can I incorporate the subtotal function in here to get a proper number. The data in the range is week numbers that correspond with activities performed each day. I need to filter how many of the weeks the activity is done. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use a function for filtered data
Thank you, thank you! This worked great once I remembered to use CTRL,SHIFT,
and ENTER! "T. Valko" wrote: =SUM(IF(FREQUENCY(A3:A83,A3:A83)0,1)) That formula will count unique numbers. Try this array formula** : =SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(A3,ROW(A3:A 83)-ROW(A3),)),A3:A83),A3:A83),1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "debl" wrote in message ... I am trying to count items in a spreadsheet using the following formula =SUM(IF(FREQUENCY(A3:A83,A3:A83)0,1)) when I filter the amount comes up the same--how can I incorporate the subtotal function in here to get a proper number. The data in the range is week numbers that correspond with activities performed each day. I need to filter how many of the weeks the activity is done. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use a function for filtered data
Thank you Ashish! This worked as well as Biff's solution!
"Ashish Mathur" wrote: Hi, First of all, we must thank Biff for sharing this solution. This is the formula to count the unique values in a filtered range. =COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A3:A83)-ROW(A2),)),MATCH(A3:A83,A3:A83,0)),ROW(A3:A83)-ROW(A2))) If this works for you, the kindly post back and thank Biff for the same. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "debl" wrote in message ... I am trying to count items in a spreadsheet using the following formula =SUM(IF(FREQUENCY(A3:A83,A3:A83)0,1)) when I filter the amount comes up the same--how can I incorporate the subtotal function in here to get a proper number. The data in the range is week numbers that correspond with activities performed each day. I need to filter how many of the weeks the activity is done. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use a function for filtered data
Hi,
Thank you. Once again Biff deserves the credit for this one - I learnt this from him -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "debl" wrote in message ... Thank you Ashish! This worked as well as Biff's solution! "Ashish Mathur" wrote: Hi, First of all, we must thank Biff for sharing this solution. This is the formula to count the unique values in a filtered range. =COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A3:A83)-ROW(A2),)),MATCH(A3:A83,A3:A83,0)),ROW(A3:A83)-ROW(A2))) If this works for you, the kindly post back and thank Biff for the same. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "debl" wrote in message ... I am trying to count items in a spreadsheet using the following formula =SUM(IF(FREQUENCY(A3:A83,A3:A83)0,1)) when I filter the amount comes up the same--how can I incorporate the subtotal function in here to get a proper number. The data in the range is week numbers that correspond with activities performed each day. I need to filter how many of the weeks the activity is done. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use a function for filtered data
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "debl" wrote in message ... Thank you, thank you! This worked great once I remembered to use CTRL,SHIFT, and ENTER! "T. Valko" wrote: =SUM(IF(FREQUENCY(A3:A83,A3:A83)0,1)) That formula will count unique numbers. Try this array formula** : =SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(A3,ROW(A3:A 83)-ROW(A3),)),A3:A83),A3:A83),1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "debl" wrote in message ... I am trying to count items in a spreadsheet using the following formula =SUM(IF(FREQUENCY(A3:A83,A3:A83)0,1)) when I filter the amount comes up the same--how can I incorporate the subtotal function in here to get a proper number. The data in the range is week numbers that correspond with activities performed each day. I need to filter how many of the weeks the activity is done. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return filtered values into report worksheet based on filtered valueon the data worksheet | Excel Worksheet Functions | |||
| Count function in Auto-Filtered data | Excel Discussion (Misc queries) | |||
| COUNTIF Function on filtered data | Excel Discussion (Misc queries) | |||
| Using Count Function on Filtered List | Excel Discussion (Misc queries) | |||
| Subtotal function with Filtered Data | Excel Discussion (Misc queries) | |||
Linear Mode
