Excel has various functions, including functions to calculate inverse cosine of a given value, to multiply 2 matrices, to estimate the internal rate of return. But, most of us(well, just me then..) use just about 5-6 formulas to do our jobs. And IF formulas are a majority of these, so it doesn’t harm to learn a few interesting things you can do with just the excel IF functions.
1. Sum alternative rows / columns:
There comes a time when you are slapped with a sheet of data and need to sum every other row in it (dont ask me why, it happens, for eg. when you copy paste your credit card statement in excel), Ofcourse, we can always type the sum function with all those arguments, but we would rather chomp on that donut while excel does the dirty work for us. Thats why it helps to know that you can sum alternative rows / columns of data using sumif() formula [syntax and examples]. Sumif function sums a range of data that meets a specific criteria. In our case the criteria will be, “if the data element is in odd number rows”.
All you need to do is add an additional column at the end of the table and fill it with 1s and 0s. (just enter 1 and 0 in 2 rows, select both of them and drag till the end of the table). Now we can use this column to test our condition by writing the sumif function as =sumif(condition range, 1, sum range) [learn how you can highlight alternative rows / columns in an excel table]
2. Count how many times each item on list A is in List B vice versa
Often when you are working on data spread across multiple sheets, it helps to know how many times each item on one list is repeated in another list(s). This can be done easily using a good old countif function. Excel countif formula counts data that meets a specific criteria. [syntax and examples]
In the above example, I have used countif function to findout how many customers are there in each city (where customer data is in List B and city data is in List A). The formula looks like =countif(condition rage, condition), eg. countif($g$32:$g$47,"chicago") would tell us how many customers are in Chicago.
3. Quickly Summarize Data with countif / sumif:
Now that we have figured out how to use sumif and countif, you can use these two functions to create quick summary of your data.
For example, we can findout, average sales per customer per city dividing total sales data per each city (obtained using a sumif) with total customers in that city (obtained using a countif) as shown below. This can be a quick way to do pivot analysis of data without actually using excel pivot tables (very useful if you are allergic to excel pivot tables or not very happy using and constantly updating them)
4. Lookup second, third … nth occurrence of an item in a list in excel:
Often we work with data that has lot of duplicates, for eg. customer phone number data that has grown over a period of time with new numbers added at the bottom of the list. Getting second, third, fourth or nth occurrence from the list can be tricky, by using a combination of countif and vlookup we can lookup nth occurrence from a list. [VLOOKUP tutorial and examples]
First in our data list we will insert another column and place the formula =current-item&countif(range till that point, item), this will append the number of smiths till that point to the end of smith, thus first smith would become smith1, second smith would become smith2, so on…
Next, when looking up smith instead of using the initial column of customer data we will use our modified customer data to fire the lookup, for eg. vlookup("smith3", lookup range, 2, false) would tell us the 3rd phone number of smith. Note the last argument to the vlookup as “false”, since our list may not be alphabetically sorted, we have to use “false” to force excel to keep looking till it finds the 3rd smith row.
5. Reduce your nested if()s to one function
Do you know that you cannot nest if functions beyond 7 levels (in earlier versions of Excel)? Thankfully, most of us never go beyond 3 or 4 levels. But why write even that many levels when you can use choose() function, which is like a switch structure in programming. A typical switch function would look like, =switch(condition, outcome1, outcome2, outcome3...), for eg, =switch(3,"good","average","poor") would return “poor” when used. The only limitation to the switch() excel function is that it accepts numbers for testing the condition. But you can overcome this with some creativity, like I have shown in the below student letter grading example:
How did I convert the letter grade to a numeric in the choose(), well thats for you to figure out 🙂
What is your favorite IF formula trick?
Well, that is for you to tell me. What is your favorite Excel IF function tip / usage? Share it in the comments, let everyone know
Download IF Formula Examples and Play with them
I have prepared an excel sheet with all these if function examples, Click here to download it and experiment.
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?