Count alpha numberic values in Excel

Question

I have a excel sheet ,sample is

Year    Month   Company Type    real sale   sale
2013    1       Sony    Tech    2.4         5
2013    2       Ikea    Home    7           1.44
2013    3       ING     Bank    5           0
2013    1       Sony    Tech    0           6
2013    2       Google  Tech    0           7
2013    4       Staples Home    5.24        8
2013    5       Sony    Tech    5           1
2013    6       ABN     Bank    2.64        1

i would like find the number of distinct comapny present on different dimension in excel pivot .i-e

Year count(Company)
2013 6
------------OR---
YEAR MONTH COUNT(COMPANY)
2013 1     1
2013 2     2
2013 3     1
2013 4     1
2013 5     1
2013 6     1

As you can see my dimension can change in pivot , so i am trying to count distinct company name in my pivot based upon the dimension , i know "counta(range)" function exists but i cannot use it in pivote table , as range is required for this function , i have a column name .

I have to do it via pivot table only , no VBA or anything else .

Thank you in advance .

Which excel version are you using? Excel 2013 has native functionality for this. Alternatively, please see this SO answer: http://stackoverflow.com/questions/11876238/simple-pivot-table-to-count-unique-values — Steph Locke, Dec 10 '13 at 11:04
Steph , can you kindly help me usnderstnad IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1) fromula — Faizan Tanveer, Dec 10 '13 at 11:28
You will probably need to read and play with SUMPRODUCT to understand. Obviously google ("excel sumproduct explained") to find something to your taste but I like http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/ as a starting point — Steph Locke, Dec 10 '13 at 11:35

score 0 · Answer 1 · answered Dec 13 '13 at 06:58

Going for your first option (Year count rather than Year, Month count) I suggest:

A PT with Company for Row Labels and no Grand Totals showing. Then insert a calculated field (say named countCo.) with Formula: counta(Company) by selecting the PT (so that PivotTable Tools shows at the top of your screen) > Options > Tools – Formulas, Calculated Field…

Drag that field into Σ Values and select Sum for it with Show values as Running Total in and Base field: Company. Result should look something like:

SO20492615 example

Count alpha numberic values in Excel

1 Answers1