Count Items

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,841
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Spyde

New Member
Joined
Feb 24, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Nov 14, 2005
Messages
1,841
Office Version
  1. 365
Platform
  1. Windows
Thank you! Very much appreciated. But it doesn't seem to work with the filtered data. Number doesn't change when I filter
 

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,841
Office Version
  1. 365
Platform
  1. Windows
But I used your recommendation of Sum product with a Subtotal and it works!
=SUMPRODUCT(SUBTOTAL(3,Approved_Req_to_Pending_PO[RequisitionID]))
 

Spyde

New Member
Joined
Feb 24, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,808
Members
416,983
Latest member
LessThanAverageUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top