Forum Discussion
How do I define text as a number so that i can formulate a cell to add up?
Hello community, Basically I want to define my "Product" cell as a sum so that in Cell F it can calculate how many different types of products I have.
For example, I have Bread and Wine therefore I want Cell F3 to show "2" because there are 2 different product types and then when I add in Cell C9 "Water" I want cell F3 to display 3 as I've added a new product to the list and if I add "Milk" in Cell C10 it displays 4 in Cell F3 etc.
Hopefully I did not make it confusing, thank you.
1 Reply
Hello,
what you describe is called a distinct count, or a unique count. Here is the Microsoft Support Article about it.
You can also build a Pivot Table with the products in the rows area, then count the resulting rows.
In newer versions of Excel that support the data model, you can add the data to the data model, then build a pivot table, drag the product into Values area and use the Distinct Count to summarize.
