Count Items

[gheyman]

I have a list, in a table, that has may or may not have duplicates. I am trying to count the number of items, but don't count duplicated items more than once. So if my column contains: A, B, C, A, D, B, A Then my formula would result in the answer of 4. it would not count A or B more than one time. My data is in a Table so I need the formula to function when its filtered. Any suggestions?

 

[Spyde]

You could use something like =SUMPRODUCT(1/COUNTIF(data,data)) | Example =SUMPRODUCT(1/COUNTIF(A1:A7,A1:A7)) should work for you. SUMPRODUCT should only count what is available in your filtered list as well. there is the Aggregate option but I have not used that inline with countif.

 

[gheyman]

Thank you! Very much appreciated. But it doesn't seem to work with the filtered data. Number doesn't change when I filter

 

[gheyman]

But I used your recommendation of Sum product with a Subtotal and it works!
=SUMPRODUCT(SUBTOTAL(3,Approved_Req_to_Pending_PO[RequisitionID]))

 

[Spyde]

But I used your recommendation of Sum product with a Subtotal and it works!
=SUMPRODUCT(SUBTOTAL(3,Approved_Req_to_Pending_PO[RequisitionID]))

that is great that the SUMPRODUCT worked for you :D