| 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
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. |
|
#7
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
subtotal only unique in filtered list
Hi JMB
It works! thank you so much for helping me...this is the first time I posted a question even though I use the page all the time to help me...so thanks very much. "JMB" wrote: 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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
subtotal only unique in filtered list
you are welcome - thanks for the feedback
"Jo" wrote: Hi JMB It works! thank you so much for helping me...this is the first time I posted a question even though I use the page all the time to help me...so thanks very much. "JMB" wrote: 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. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
subtotal only unique in filtered list
Hi JMB
A while ago you helped me with this formula...I wondered could you help me again please as I now have to add more info to it. Columns are same A B C co Pos Count ..5 121 in ..7 123 in ..9 124 in ..9 124 in ..2 125 out I basically need to do the same as before (count of unique Pos No entries that are visible and "in") = 3 But i also now need to multiple it by the Co as well Therefore it will now equal 2.1 Are you able to help me with this please? Thanks in advance. "JMB" wrote: 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. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
subtotal only unique in filtered list
try this formula - adjust ranges as needed:
=SUMPRODUCT(--(MATCH(B2:B8&"",B2:B8&"",0)=ROW(B2:B8)-ROW(B2)+1),SUBTOTAL(3,OFFSET(B1,ROW(B2:B8)-ROW(B1),,1)),--(C2:C8="in"),A2:A8) gives me 2.1. The original formula I gave you could be made a little shorter: =SUMPRODUCT(--(MATCH(B2:B8&"",B2:B8&"",0)=ROW(B2:B8)-ROW(B2)+1),SUBTOTAL(3,OFFSET(B1,ROW(B2:B8)-ROW(B1),,1)),--(C2:C8="in")) gives me 3. Both formulas can be entered normally - no need for Cntrl+Shift+Enter like the last one. "Jo" wrote: Hi JMB A while ago you helped me with this formula...I wondered could you help me again please as I now have to add more info to it. Columns are same A B C co Pos Count .5 121 in .7 123 in .9 124 in .9 124 in .2 125 out I basically need to do the same as before (count of unique Pos No entries that are visible and "in") = 3 But i also now need to multiple it by the Co as well Therefore it will now equal 2.1 Are you able to help me with this please? Thanks in advance. "JMB" wrote: 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. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
subtotal only unique in filtered list
Thanks so much JMB. both of them work fantastically!
Thanks Again "JMB" wrote: try this formula - adjust ranges as needed: =SUMPRODUCT(--(MATCH(B2:B8&"",B2:B8&"",0)=ROW(B2:B8)-ROW(B2)+1),SUBTOTAL(3,OFFSET(B1,ROW(B2:B8)-ROW(B1),,1)),--(C2:C8="in"),A2:A8) gives me 2.1. The original formula I gave you could be made a little shorter: =SUMPRODUCT(--(MATCH(B2:B8&"",B2:B8&"",0)=ROW(B2:B8)-ROW(B2)+1),SUBTOTAL(3,OFFSET(B1,ROW(B2:B8)-ROW(B1),,1)),--(C2:C8="in")) gives me 3. Both formulas can be entered normally - no need for Cntrl+Shift+Enter like the last one. "Jo" wrote: Hi JMB A while ago you helped me with this formula...I wondered could you help me again please as I now have to add more info to it. Columns are same A B C co Pos Count .5 121 in .7 123 in .9 124 in .9 124 in .2 125 out I basically need to do the same as before (count of unique Pos No entries that are visible and "in") = 3 But i also now need to multiple it by the Co as well Therefore it will now equal 2.1 Are you able to help me with this please? Thanks in advance. "JMB" wrote: 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. |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
subtotal only unique in filtered list
quite welcome - thanks for posting back to let us know it is working okay.
"Jo" wrote: Thanks so much JMB. both of them work fantastically! Thanks Again "JMB" wrote: try this formula - adjust ranges as needed: =SUMPRODUCT(--(MATCH(B2:B8&"",B2:B8&"",0)=ROW(B2:B8)-ROW(B2)+1),SUBTOTAL(3,OFFSET(B1,ROW(B2:B8)-ROW(B1),,1)),--(C2:C8="in"),A2:A8) gives me 2.1. The original formula I gave you could be made a little shorter: =SUMPRODUCT(--(MATCH(B2:B8&"",B2:B8&"",0)=ROW(B2:B8)-ROW(B2)+1),SUBTOTAL(3,OFFSET(B1,ROW(B2:B8)-ROW(B1),,1)),--(C2:C8="in")) gives me 3. Both formulas can be entered normally - no need for Cntrl+Shift+Enter like the last one. "Jo" wrote: Hi JMB A while ago you helped me with this formula...I wondered could you help me again please as I now have to add more info to it. Columns are same A B C co Pos Count .5 121 in .7 123 in .9 124 in .9 124 in .2 125 out I basically need to do the same as before (count of unique Pos No entries that are visible and "in") = 3 But i also now need to multiple it by the Co as well Therefore it will now equal 2.1 Are you able to help me with this please? Thanks in advance. "JMB" wrote: 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. |
| 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) | |||
Linear Mode
