Comparing the Two Solutionsīoth of these solutions are great because they can be refreshed when new data is added to the source table. The Distinct Count function goes through the Deal ID column and gives us a count of the unique values, so our summary report will look just like it did for Solution #1. This will open a window where we can choose Distinct Count as a calculation type. To fix this, we want to right-click on the Sum of Deal ID column header and select Value Field Settings. That initially gives us numbers we don't want in our summary report. When you build your pivot table this time, you are going to drag Deal ID to the Sum of Values area. (Data Model is another term for PowerPivot.) This time, when we create our pivot table, we are going to check the box that says Add this table to the Data Model. We still want our data formatted as an Excel Table, but we don't need a helper column for this solution. This solution is only available for versions of Excel that are 2013 or later for Windows. The nice thing about using a pivot table is that as we add or delete source data entries, we can refresh the pivot table ( Alt + F5 ) to include those changes. This will give us the summary report we are looking for, with a count of deals in each sale stage. To create our summary report using the new pivot table, put the Sales Stage in the Rows area and Deal Count in the Sum of Values area. Now that we have these fractions that will give us a distinct count when we create our pivot table, we can go ahead and create the pivot table by choosing Pivot Table on the Insert tab. The change to the formula can be seen in green here: If we divide the formula into the number 1, we will get fractions in each of those cells that when added together will count one entry for each deal.
The formula will return the number of rows for each Deal ID number. Use the COUNTIF function, with the range being the Deal ID column, and the criteria being the cell in the Deal ID column that corresponds with the row you are in. Now that your data is in Table format, add a helper column to the right of the table and label it Deal Count. Hit OK when the Format as Table window appears. Right-click on the table format you want and select Apply and Clear Formatting. To do that, just select any cell in the data set, and click on Format as Table on the Home tab. Start by turning your data into an Excel Table. The great thing about this solution is that it can be used in any version of Excel. So we have to find a way to create a distinct count (counting unique rows) for each deal so that we can sum them up.īy the way, thank you to anyone who submitted a solution to the data challenge! There were a lot of great submissions. To summarize the challenge, we want to create a summary report of deal count by stage, but there are multiple rows per deal in the CRM data. These two methods were submitted as solutions to the data analysis challenge that you can find here: In this post, we're going to take a look at two different ways to do a distinct count using pivot tables.
#Excel mac countif conditions for two columes download#
I've included both the original file and the solution file for you to download here:ĭata Analysis Challenge - Pivot Table Solution FINAL.xlsx (118.4 KB) Counting Unique Rows The COUNTIFS function calculates the number of cells across multiple ranges that equals all given conditions.ĬOUNTIFS($B$6:$B$11, $B$6:$B$11, $C$6:$C$11, $C$6:$C$11, $D$6:$D$11, $D$6:$D$11)ĬOUNTIFS() and returns 2 in cell D3.Watch on Youtube & Subscribe to our Channel Download the Excel File
Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.Search values and return value with highest levelĮxtract unique distinct records from two data setsįilter unique distinct records using criteria Count cells based on background color programmaticallyĬreate a list of duplicates where adjacent cell value meets a conditionĭistribute values across numerical ranges
0 kommentar(er)
