We all know about the MAX formula. But do you know about 3D Max?
Sounds intriguing? Read on.
Lets say you are the sales analyst at ACME Inc. Your job involves drinking copious amounts of coffee, creating awesome reports & helping ACME Inc. beat competition.
For one of the reports, you need to find out the maximum transactions by any customer across months.
But there is a twist in the story.
Your data is not in one sheet. It is in multiple sheets, one per month.
Like this:
How to get the max value for all months?
Using 3D MAX formula
We can use 3D formulas in such cases. 3D formula?!?
Lets say our transaction data is in column C, in range C5:C44 in all sheets (same cells in all sheets)
To calculate the max of all the transactions, we simply write:
=MAX(Jan:Jun!C5:C44)
Notice the blue text? That is what makes our references 3D.
Aside: If row & columns make Excel 2D, sheets in a workbook act as 3rd dimension. Hence the name 3D reference.
This formula will go and fetch all the C5:C44 data from sheets Jan thru Jun and gives us the desired answer.
Related: Consolidating data from multiple sheets using 3D references.
What if you want to consider only specific months
The 3D formula approach is simple & powerful. But what if you want to consider data only in a specific list of sheets (or months in our case)?
For example, what formula would work if we want to calculate maximum transactions in months Jan, Mar, Apr & Jun alone?
Lets say the names of the sheets we want to consider is listed in a range called sheet.names
Also, keep in mind that the data is in range C5:C44 in all the sheets.
Then the below formula gives us maximum value from the selected sheets.
{=MAX(N(INDIRECT(ADDRESS(ROW(A5:A44),3,1,1, TRANSPOSE(sheet.names)))))}
It is an array formula. So you must press CTRL+Shift+Enter to get the correct result.
PS: Thanks to Pranay Shah, whose question inspired me to write this formula.
How does it work?
First lets figure out the logic we need to use.
- We have a list of sheet names in the range sheet.names
- For each sheet, get the data from cells C5:C44
- Calculate the max of all this data
Now, lets take a look at the formula, inside out.
ROW(A5:A44) portion: This generates an array for numbers from 5 to 44 – {5;6;7;…;42;43;44}
Transpose(sheet.names) portion: This transposes the vertical sheet names array to horizontal. So {“Jan”;”Mar”;”Apr”;”Jun”} becomes {“Jan”,”Mar”,”Apr”,”Jun”}
ADDRESS(ROW(),3,1,1,TRANSPOSE()): This generates an array of cell addresses from rows 5 to 44, column 3 and sheets in sheet.names range. The result looks like this:
{“Jan!$C$5″,”Mar!$C$5″,”Apr!$C$5″,”Jun!$C$5”; “Jan!$C$6″,”Mar!$C$6″,”Apr!$C$6″,”Jun!$C$6”;
“Jan!$C$7″,”Mar!$C$7″,”Apr!$C$7″,”Jun!$C$7”; “Jan!$C$8″,”Mar!$C$8″,”Apr!$C$8″,”Jun!$C$8”;
“Jan!$C$9″,”Mar!$C$9″,”Apr!$C$9″,”Jun!$C$9”; “Jan!$C$10″,”Mar!$C$10″,”Apr!$C$10″,”Jun!$C$10”;
…
“Jan!$C$41″,”Mar!$C$41″,”Apr!$C$41″,”Jun!$C$41”; “Jan!$C$42″,”Mar!$C$42″,”Apr!$C$42″,”Jun!$C$42”;
“Jan!$C$43″,”Mar!$C$43″,”Apr!$C$43″,”Jun!$C$43”; “Jan!$C$44″,”Mar!$C$44″,”Apr!$C$44″,”Jun!$C$44”}
Why TRANSPOSE()?
If we have not TRANSPOSE()ed either sheet.names or row numbers, we will not get full list of addresses. TRANSPOSE forces Excel to generate all combinations of addresses from given row numbers & sheet names.
For example, here is the result of the formula
ADDRESS(ROW(A5:A44),3,1,1, sheet.names)
Notice the missing TRANSPOSE()
{“Jan!$C$5″;”Mar!$C$6″;”Apr!$C$7″;”Jun!$C$8”;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A; #N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A; #N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A; #N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A}
INDIRECT(ADDRESS()) portion: We have the addresses and we need values. This is exactly the purpose of INDIRECT formula. So we pass the list of addresses to INDIRECT to get the cell values.
This results in an array of numbers like this:
{1400,900,1225,1440; 1035,1300,850,1850; 990,2000,1140,775; 1520,870,1225,650; 1300,1000,1800,875; 1305,980,1085,1215; 750,1350,1000,1330; 1050,600,1125,1755; 990,735,1350,1600; 1215,1750,770,625; 1600,735,1305,1300; 960,1950,1480,1800; 1215,1365,1110,1395; 1320,910,750,1560; 1700,975,1125,1480; 900,1400,780,1300; 1485,1440,960,1300; 825,2125,1110,1215; 1000,945,810,1120; 1650,500,1170,990; 1440,1080,1110,840; 1035,840,1300,800; 1225,1330,1020,1560; 1100,690,1170,780; 600,700,1280,990; 1000,1000,1400,700; 1260,1520,875,1305; 1360,1260,925,1320; 810,1100,2000,1800; 825,690,750,1215; 1575,1560,1000,1900; 1190,1080,960,1400; 1200,1200,1160,980; 900,1665,575,500; 880,1000,1200,1550; 1000,950,1440,550; 1400,900,1000,1190; 750,1190,1110,700; 1710,805,800,1755; 1950,1365,660,1150}
Now, notice the 2 dimensional nature of this array. It has 4 items per row.
N(INDIRECT()) portion:
We just pass the array of numbers to N() so that they are force converted to numbers. This step ensures that we get correct results with MAX.
Note: Even without N() your array formula shows a result, but often this will be incorrect. I assume it is one of the quirks of Excel and we just have to use N().
Related: See how N() plays a vital role in situations like – dynamic charts from non-contiguous data & String parsing.
MAX(N()) portion:
This will just tell us what the maximum number in that array. Make sure you press CTRL+Shift+Enter to get the correct result.
Download Example Workbook
If all these MAX formulas are confusing, check out the example workbook. It shows all these. Play with the formulas and examine the results to learn more.
Do you use 3D references?
I rarely use them. This is because, most of the times, my data is in one place. If is it scattered across multiple sheets, I usually spend time writing a macro (or using Power Query) to consolidate the data to one place before attacking the analysis problems.
But I find 3D references & formulas a powerful way to answer questions like this.
What about you? Do you use 3D references in your formulas? When do you use them? Please share your thoughts & experiences using comments.
Learn more
If this technique sounds interesting, check out below tutorials to learn more.
- MaxIF formula in Excel
- Using INDEX formula in Excel
- Calculate maximum change – problem & Solution
- Calculating all-time high & trailing 12 months high values
21 Responses to “Distinct count in Excel pivot tables”
The distinct count option works well but I have found that if I have a date field and want to group by year, month, etc. that option seems to be disabled. I need to do both, distinct count and group by year/month.
Example data; sales orders with item quantities with dates.
Challenge; sum the item quantities, count the distinct orders and group by month. How do I do this?
Perhaps that's not possible due to the grouping?
@Al... When you use data model based pivots, you cannot group values manually anymore. Why not use Excel 2016's default date grouping option? In this case we have just a few dates, so Excel is not grouping them, but if you have an year's worth of data, when you make the pivot with date in the row label area, Excel automatically groups them. If you have fewer dates or want to use your own grouping, just create a table with all dates, add columns with month, week, year etc. Then connect this table (these types of tables are usually called as calendar tables) to your data on date field as a relationship. Now you can create reports by month, quarter etc easily.
Is this the only way to do it in 2013? I find it rather cumbersome to have to create another data table listing dates with the another column for MONTH() and YEAR() to be able to summarise data for senior level...
I know people find adding calendar tables cumbersome, but it is a best practice and let's you add more layers of analysis quite easily. For example, adding analysis by weekday vs. weekend or by financial quarter or YTD calculations (you would need either Power Pivot DAX or some very carefully setup pivot table value field settings)
I had absolutely no idea this was possible. Very useful, nice work!
Doesn't work for 2010 version though (or at least not my works version)
Hi ,
The post has the following in it :
These instructions work only in Excel 2016, Office 365 and Excel 2013.
when i have 2 different Pivot tables, one without the enabled “Add this data to data model” option, and the other one with it enabled.. is there anyway i can link slicers between them?
if the answer is NO,, what to do ?
Quick note, the “Add this data to data model” option is not available for the Mac version.
perhaps outside scope of this article but I have found when I attempt to create a pivot table from an external data source (connection to a sql view) the "Add this data to data model" becomes greyed out. Anybody experienced and found a solution so I can start getting distinct count in my pivot tables?
Is there a way to still add a calculated field when using distinct count?
I found I can't change the date source after tick the " add this data to the data model", can you help to adv how to change the date source in such case?
Is there a way to update the source once you have added to the data model? I receive a new spreadsheet weekly and would like to update the connection so my tables pull from the new source.
Hi Crhis, I like how you have hulk (superhero) as your avatar. Do you know that there is a superhero in Excel too? It's Power Query. You can use it to solve your problem in a simple click. Here an intro if you need some guidance.
Powerful Introduction to Power Query
A big Thank you. It worked.
Hi, have survey data that I need to analyze but the challenge is that my key fields are showing horizontally. I tried to transpose the fields using Power Query, but unfortunately the new fields are returning same values on a pivot table despite using distinct values
How I can a do a pivot table with discount conts in some columns and then generate shor report filter pages. pls it drives crazy
Hi. Why grand total pivot of distinct count is 13? shouldn't it be 67?
Great Answer! Saved me lots of time!
Thank you!!!
Worked awesome! Thanks!!
Hi Chandoo,
I am using pivot tables for distinct count and now I need to update them with new set of data. But when I update the source data, all the columns and formatting of Pivot table disappears and I need to build it from Scratch.
Is there a possibility that I can update the source data with new rows added and also retain my pivot tables?