📗 Download This Video Workbook: »» cutt.ly/up4excel09
🎯 SUM or COUNT by cell colour using the hidden Excel function GET.CELL - Just one of the 66 operations this function can perform. Unlock a whole new world of long forgotten Excel Functions using the 2 methods taught in this video.
👨🏫 Remove On-Screen Tutor: »» • Unveiling Excel's Hidd...
You can’t normally count by cell color in Excel, but if you make use of Excel 4.0 macro functions you can reveal this functionality. In order to count by cell colour in Excel you can use the GET.CELL function. Technically it is the GET.CELL macro function because Excel treats it as an Excel 4 macro. GET.CELL() has various options but GET.CELL 63 is the one that detects cell color and therefore allows you to sum by cell color, count by cell colour, sum only certain color cells, and sumif by cell color. As if that wasn’t impressive enough, GET.CELL 24 can be used to identify the font color so the same technique can be used to sum by font color, count by font colour, sum only certain color fonts, and sumif by font color. The GET.CELL formula can also be used to sum only bold cells. The GET.CELL macro formula is a truly useful legacy function that can still be accessed in Excel 2019 and Excel 365. You can impress those around you by using this secret GET.CELL function in Excel as a sum color Excel formula today, and do something others think is not possible like sum bold cells in Excel.
=GET.CELL(63,[A1]) detects the fill colour of any cell....but it doesn't work on a modern Excel sheet unless you apply one of the techniques in this video.
Once you know how to access this function you can SUM, COUNT, AVERAGE, MAX, MIN etc by almost any cell format including Font Size, Font Colour, Font Name and even Row Height.
The full list of operations and formats this function can detect is in the video workbook....so download on the link above.
Furthermore, the methods revealed to unlock this function also apply to numerous other secret legacy Excel functions - all of which were hidden after 1992 !!
A whole new world of Excel Functions will be at your disposal from now on.
GET.CELL(type_num, reference)
Type_num Returns
17 Row Height
18 Font Name
19 Font Size
24 Font Colour
63 Fill Colour
+ Many Others
In this Excel tutorial, I'll show you how to efficiently sum or count cells by color using a feature dating back to Excel's XL4 macro language from 1992. By the end of this video, you'll master the art of analyzing and manipulating data based on cell color, opening up new possibilities in Excel.
Topics covered include:
1. Introduction to Get.Cell Function: We'll explore function number 63, which retrieves the fill color of a cell, laying the groundwork for our color-based calculations.
2. Utilizing Named Ranges: Learn how to create a named range incorporating Get.Cell function, facilitating seamless referencing of cell colors in formulas.
3. Summing Cells by Color: Discover how to use SUMIF function alongside our named range to efficiently sum cells based on color, streamlining data analysis.
4. Counting Cells by Color: I'll guide you through the process of counting cells by color using the COUNTIF function, providing valuable insights into your dataset.
5. Forcing Recalculation: Learn a workaround to ensure automatic updates of color-based calculations, ensuring accuracy in your Excel workflow.
6. Using Macro Sheets: Explore an alternative method for leveraging macro functions directly within Excel worksheets, eliminating the need for named ranges.
7. Caveats and Considerations: Understand limitations, such as compatibility issues with conditional formatting, and strategies to overcome potential challenges.
Throughout the tutorial, I'll adopt a straightforward approach, providing actionable steps for immediate implementation. Practice along with the provided example data or download the accompanying spreadsheet to reinforce your skills.
Excel offers a plethora of macro functions waiting to be explored beyond Get.Cell. Share your favorite techniques in the comments below for future video ideas!
I'm confident you'll find this tutorial informative and valuable for your Excel journey. If you do, don't forget to like, subscribe, and stay tuned for more insightful content. Happy Excel-ing!
14 июл 2024
