Formula to Calculate Subtotal - Count of #'s Column While Avoiding Duplicates

Hello,

I am attempting to add a formula to my spreadsheet that will calculate the count of unique numbers in a column when I use multiple filters (while avoiding counting the duplicates.) I have browsed multiple threads on this topic, but so far no solution has worked for me. I am hoping someone can help me solve this. My number range starts in cell P12 and goes through P44 as of now, however I would like to extend that range to P1048576 if possible.

I have tried this example from another thread and it failed to yield the desired solution... (Yielded a Value error)

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(P12,ROW(P12:P44)-ROW(P12),0)),MATCH(P12:P44,P12:P44,0)),ROW(P12:P44)-ROW(P12)+1),1))

Any help would be appreciated!

Thanks

Thanks for the reply! The formula worked for me, however when I go to filter it does not recalculate based on what I have filtered on. Do you know how to alter the formula to account for this? Based on some things I have read, the Subtotal function should be involved.

Thanks for your reply Tony!

The formula is calculating correctly when no filters are on, however when I go to add filters, it is calculating the wrong # for me. For instance, it was showing 4 when it should have showed 8 and 22 when it should have shown 12. Any other suggestion I could try? Again, I am looking for a Subtotal when filtered that display the count of a column while avoiding duplicate entries. I appreciate the help!!

Ok perfect!! The formula seems to be working now... I know I entered it as an array but it looks like it didn't take or somehow reverted back.

Problem solved!! Thanks again for the help Tony, you were of great assistance!!

Actually Tony, I have 1 more question if you dont mind... Is there a way to adjust this formula so that it doesnt add to the count if I have a blank value in the corresponding row, but in column O?

This is my formula as of now:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(P12,ROW(P12:P1048576)-ROW(P12),0)),P12:P1048576),P12:P1048576),1))

However, if O 15 is blank, I dont want P15 to count towards the subtotal in the above formula. Does that make sense?

Thanks!

Hey Tony,

I entered the array as follows: =SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(P12,ROW(P12:P86)-ROW(P12),0)),IF(O12:O86<>"",P12:P86)),P12:P86),1)) and am receiving a #value! error.

Does this look right to you, and if not, any other suggestions that I could try?

Thanks again!

Yep that was it, thought I had done it previously but I guess not. Got it working now:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(P12,ROW(P12:P1048576)-ROW(P12),0)),IF(O12:O1048576<>"",P12:P1048576)),P12:P1048576),1))

I am sorry to do this to you, but I still need a little more help... I am still new to excel and can't manipulate the formuals the way I would like to yet...

It looks like my formula is still counting results in Column O that are negative number values.

For example:

Column O Column P
150 7
-36 15
21
50 24

So in this instance, I would like my formula to exclude the -36 , in addition to the blanks from column O that it is already including, and give me a result of 2.

I think once we get this part figured out, everything should be perfect!!

Thanks again so much!

Not a problem Tony, I have done the same. In the meantime I had this issue solved and will mark it as such. Again, I appreciate all the help you provided in this thread.