| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtotal only unique in filtered list
I have a spreadsheet that has for example two columns (filters are on this
data) a b 1 in 1 in 2 out 2 out 3 in 3 in I have written the formula =SUMPRODUCT((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$8)-ROW($B$1),,1))*($B$2:$B$8="IN"))) However, when I filter to 1 it says two but I want it to say 1 because I want it to count unique only. can someone please help me. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
subtotal only unique in filtered list
When you filter column A for 1, you want to count the number of unique
visible cells in column B? You could try: =SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$8 )-ROW($B$1),,1)),MATCH($B$2:$B$8,B2:B8,0),""),IF(SUB TOTAL(3,OFFSET($B$1,ROW($B$2:$B$8)-ROW($B$1),,1)),MATCH($B$2:$B$8,B2:B8,0),""))0)) array entered w/Cntrl+Shift+Enter (or you get #VALUE!) "Jo" wrote: I have a spreadsheet that has for example two columns (filters are on this data) a b 1 in 1 in 2 out 2 out 3 in 3 in I have written the formula =SUMPRODUCT((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$8)-ROW($B$1),,1))*($B$2:$B$8="IN"))) However, when I filter to 1 it says two but I want it to say 1 because I want it to count unique only. can someone please help me. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
subtotal only unique in filtered list
Hi JMB
I actually want to filter Column B to In and then only count the unique numbers in Column 1. Expecting to see 2 as answer. I put this formula in and it returns #N/A? "JMB" wrote: When you filter column A for 1, you want to count the number of unique visible cells in column B? You could try: =SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$8 )-ROW($B$1),,1)),MATCH($B$2:$B$8,B2:B8,0),""),IF(SUB TOTAL(3,OFFSET($B$1,ROW($B$2:$B$8)-ROW($B$1),,1)),MATCH($B$2:$B$8,B2:B8,0),""))0)) array entered w/Cntrl+Shift+Enter (or you get #VALUE!) "Jo" wrote: I have a spreadsheet that has for example two columns (filters are on this data) a b 1 in 1 in 2 out 2 out 3 in 3 in I have written the formula =SUMPRODUCT((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$8)-ROW($B$1),,1))*($B$2:$B$8="IN"))) However, when I filter to 1 it says two but I want it to say 1 because I want it to count unique only. can someone please help me. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
subtotal only unique in filtered list
Use JMB's formula but change all the references to column A.
Biff "Jo" wrote in message ... Hi JMB I actually want to filter Column B to In and then only count the unique numbers in Column 1. Expecting to see 2 as answer. I put this formula in and it returns #N/A? "JMB" wrote: When you filter column A for 1, you want to count the number of unique visible cells in column B? You could try: =SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$8 )-ROW($B$1),,1)),MATCH($B$2:$B$8,B2:B8,0),""),IF(SUB TOTAL(3,OFFSET($B$1,ROW($B$2:$B$8)-ROW($B$1),,1)),MATCH($B$2:$B$8,B2:B8,0),""))0)) array entered w/Cntrl+Shift+Enter (or you get #VALUE!) "Jo" wrote: I have a spreadsheet that has for example two columns (filters are on this data) a b 1 in 1 in 2 out 2 out 3 in 3 in I have written the formula =SUMPRODUCT((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$8)-ROW($B$1),,1))*($B$2:$B$8="IN"))) However, when I filter to 1 it says two but I want it to say 1 because I want it to count unique only. can someone please help me. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
subtotal only unique in filtered list
Hi Biff
I tried to do that but it is still returning #N/A? "Biff" wrote: Use JMB's formula but change all the references to column A. Biff "Jo" wrote in message ... Hi JMB I actually want to filter Column B to In and then only count the unique numbers in Column 1. Expecting to see 2 as answer. I put this formula in and it returns #N/A? "JMB" wrote: When you filter column A for 1, you want to count the number of unique visible cells in column B? You could try: =SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$8 )-ROW($B$1),,1)),MATCH($B$2:$B$8,B2:B8,0),""),IF(SUB TOTAL(3,OFFSET($B$1,ROW($B$2:$B$8)-ROW($B$1),,1)),MATCH($B$2:$B$8,B2:B8,0),""))0)) array entered w/Cntrl+Shift+Enter (or you get #VALUE!) "Jo" wrote: I have a spreadsheet that has for example two columns (filters are on this data) a b 1 in 1 in 2 out 2 out 3 in 3 in I have written the formula =SUMPRODUCT((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$8)-ROW($B$1),,1))*($B$2:$B$8="IN"))) However, when I filter to 1 it says two but I want it to say 1 because I want it to count unique only. can someone please help me. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
subtotal only unique in filtered list
Assuming Co is Column A, Pos No is Column B, and Count is Column C, try
=SUM(--(FREQUENCY(IF((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$ 8)-ROW($B$1),,1)))*($C$2:$C$8="in"),$B$2:$B$8,""),IF( (SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$8)-ROW($B$1),,1)))*($C$2:$C$8="in"),$B$2:$B$8,""))0) ) array entered w/Cntrl+Shift+Enter. Adjust the ranges as needed. Should give you a count of unique Pos No entries that are visible and "in". "Jo" wrote: Hi Biff I tried to do that but it is still returning #N/A? "Biff" wrote: Use JMB's formula but change all the references to column A. Biff "Jo" wrote in message ... Hi JMB I actually want to filter Column B to In and then only count the unique numbers in Column 1. Expecting to see 2 as answer. I put this formula in and it returns #N/A? "JMB" wrote: When you filter column A for 1, you want to count the number of unique visible cells in column B? You could try: =SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$8 )-ROW($B$1),,1)),MATCH($B$2:$B$8,B2:B8,0),""),IF(SUB TOTAL(3,OFFSET($B$1,ROW($B$2:$B$8)-ROW($B$1),,1)),MATCH($B$2:$B$8,B2:B8,0),""))0)) array entered w/Cntrl+Shift+Enter (or you get #VALUE!) "Jo" wrote: I have a spreadsheet that has for example two columns (filters are on this data) a b 1 in 1 in 2 out 2 out 3 in 3 in I have written the formula =SUMPRODUCT((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$8)-ROW($B$1),,1))*($B$2:$B$8="IN"))) However, when I filter to 1 it says two but I want it to say 1 because I want it to count unique only. can someone please help me. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
subtotal only unique in filtered list
Hi
I have got this formula to work and it will tell me the correct numbers when I apply the filter. However, can I also get it to tell me for instance if I don't apply any filters that there are 3 position nos (unique id) that equal in (count column)?. I would also need the formula to work if I applied the filter to Co and selected org because then I would have 2 positions that are in. Hope this makes sense. Co pos no count org 101 in org 119 in adj 211 out adk 210 out org 101 in one 301 in "JMB" wrote: When you filter column A for 1, you want to count the number of unique visible cells in column B? You could try: =SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$8 )-ROW($B$1),,1)),MATCH($B$2:$B$8,B2:B8,0),""),IF(SUB TOTAL(3,OFFSET($B$1,ROW($B$2:$B$8)-ROW($B$1),,1)),MATCH($B$2:$B$8,B2:B8,0),""))0)) array entered w/Cntrl+Shift+Enter (or you get #VALUE!) "Jo" wrote: I have a spreadsheet that has for example two columns (filters are on this data) a b 1 in 1 in 2 out 2 out 3 in 3 in I have written the formula =SUMPRODUCT((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$8)-ROW($B$1),,1))*($B$2:$B$8="IN"))) However, when I filter to 1 it says two but I want it to say 1 because I want it to count unique only. can someone please help me. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array Formulas - Unique List from List with Duplicates | Excel Discussion (Misc queries) | |||
| Subtotals in a list | Excel Discussion (Misc queries) | |||
| list unique values in a column | Excel Worksheet Functions | |||
| How do I paste data into filtered list in Excel? | Excel Discussion (Misc queries) | |||
| Pull unique names for drop down list | Excel Discussion (Misc queries) | |||
Hybrid Mode
