See how to count unique values in Excel with a formula, and count distinct values automatically in a pivot table. Apart from the basic Excel count unique values formula, you will learn formulas for counting unique names, texts, numbers, cased-sensitive unique values, and more. To obtain a distinct count in an Excel 2013 PivotTable, here is what we do. We select any cell in the data source, as normal, and then click Insert > PivotTable, as normal. However, we need to add this data source to the Data Model.
How to work with the All Colors tab Count & Sum by Color app will become your reliable assistant in adding or counting cell values grouped by colors. If you are fond of using different color codes to highlight certain parts of data, this helpful app will show you Sum, Count, Average, Min or Max for the colored cells. If you need to sum or count all highlighted cells in Excel 2011, first open the worksheet with your data and run the Count & Sum by Color app by clicking on its name under the Ablebits.com tab.
You'll see the Count & Sum by Color pane open. Click on the All Colors tab. Highlight the needed range in your table and you will see its address in the Select range field. It's also possible to click on the Select range button to pick the necessary area manually.
Now you can specify the color option by selecting the Background or Font radio button. Press the Background radio button to calculate by the color of cells. Select the Font radio button to process the selected values by their text color. Press the Calculate button to process the range. The results will be displayed on the Count & Sum by Color pane grouped by color codes.
The app will quickly perform all math operations on your data. You can then pick any function from the Show results for drop-down list to see the needed calculation results.
Sum – add all the cell values. Count – count the number of cells. Average – determine the average of the values.
Min – see the highest number. Max – show the lowest cell value. This handy calculating tool also lets you paste the results from the app's pane to any location on your spreadsheet. Click on the Paste results button to see the Ablebits.com Count & Sum by Color dialog box. Select any cell in your current or new worksheet for adding the numbers and click OK to see the calculation results added. How to work with the One Color tab If you use multiple different colors to group and highlight data in your Excel 2011 tables, you may need to pick certain color and use it to calculate cell values. The One Color tab on the Count & Sum by Color pane lets you instantly calculate cell data based on the color you select.
Open the Excel table where you need to calculate cell values and run the Count & Sum by Color app by clicking on its name under the Ablebits.com tab. Click on the One Color tab to start.
Select the range in your table and you will see its address in the Select range field. You can also click on the Select range button to pick the necessary range manually.
Now you need to specify the correct color by choosing the cell that contains the necessary fill or font in your table. To do this, click on the Select range button next to the Pattern cell field. You'll get the Ablebits.com Count & Sum by Color dialog box letting you select a pattern cell with the right color.
The address of the selected cell will automatically appear in the Pattern cell field. Define if you want to use the background or font color for calculation. Click the Background radio button to count the selected data by the fill color.
Select Font if it’s the text color you want to use. Finally press the Calculate button. On the add-in pane you'll instantly see Sum, Count, Average, Min and Max calculated for the cell data of the specified color. If you want to paste the results to your table, just click on the Paste results button. You'll get the Ablebits.com Count & Sum by Color dialog box letting you pick the top left cell for pasting the calculations. Just click on the needed cell and click OK on the dialog box.
Feel free to use the One Color tab each time you need to see the results just for the color you specify. The Count & Sum by Color app will save you much time on calculating and recalculating highlighted data in your Excel for Mac. Besides summing and counting values in the colored cells, it also allows applying Average, Max or Min functions to the highlighted range. Choose this tool for quick and reliable color calculations.
I don't think you can do this with a custom number format. Put the following function in a module: Function FormatMAC(varMAC) As String Dim i As Long For i = 1 To Len(varMAC) 2 FormatMAC = Mid(varMAC, Len(varMAC) + 1 - 2. i, 2) & ':' & FormatMAC Next i If FormatMAC ' Then FormatMAC = Left(FormatMAC, Len(FormatMAC) - 1) End If End Function With MAC addresses in A1:A100, you can enter =FormatMAC(A1) in cell B1 and fill down. If you'd rather update the values themselves, select the range with MAC addresses and run the following macro (which uses the FormatMAC function): Sub FormatMACSelection Dim rng As Range Application.ScreenUpdating = False For Each rng In Selection rng.Value = FormatMAC(rng.Value) Next rng Application.ScreenUpdating = True End Sub Regards, Hans Vogelaar.
I don't think you can do this with a custom number format. Put the following function in a module: Function FormatMAC(varMAC) As String Dim i As Long For i = 1 To Len(varMAC) 2 FormatMAC = Mid(varMAC, Len(varMAC) + 1 - 2. i, 2) & ':' & FormatMAC Next i If FormatMAC ' Then FormatMAC = Left(FormatMAC, Len(FormatMAC) - 1) End If End Function With MAC addresses in A1:A100, you can enter =FormatMAC(A1) in cell B1 and fill down. If you'd rather update the values themselves, select the range with MAC addresses and run the following macro (which uses the FormatMAC function): Sub FormatMACSelection Dim rng As Range Application.ScreenUpdating = False For Each rng In Selection rng.Value = FormatMAC(rng.Value) Next rng Application.ScreenUpdating = True End Sub Regards, Hans Vogelaar. Ok figured out how to add a module. Alt - F11 opens the VBA side.
But in Excel 2007 there is NOTHING in any of the menus there that indicates this, i had to find out from reading articles. So i copied/pasted the function, and saved it, but in the spreadsheet if i try to enter a function =FormatMAC(a1) then hit enter, it, the cell, says 'Name?'
As if it cannot find it at all. When powering on the computer today and opening up that file, it had a warning message that a macro is there from a source that cannot be verified, enable macros anyway? So it looks like Excel at least found it, but in the worksheet it will not let me use it. Ok, got it to work, i added it again as a module as this time it saw it. So the module as written, when trying to use formatmac(a1) it says syntax error, but formatmac produces 11:22:33:44:55 ok. Now it totally works. Instead of me typing in the cell, a1, i clicked on the cell and it highlighted and did whats expected.
After doing just 1 cell, moving the mouse around to the lower right corner it turned into a plus sign, i clicked on the finished cell and dragged or drug downward and it filled in the rest of them just fine. (i was probably trying too hard to make that happen before).
I tried this with the upper function and it also worked; works with alpha and numeric thanks again Hans! Brad R Windows and Mobile platform Developer California USA.