Unveiling Excel's Hidden Gem: Sum and Count by Cell Color Made Easy
📗 Download Workbook: »» https://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.
👨🏫 On-Screen Tutor Version: »» • Uncover Hidden Excel Powers: Sum & Co...
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 tutorial, I'll show you how to efficiently sum or count cells by color in Microsoft Excel, bypassing the need for manual filtering or creating complex Visual Basic functions. We'll delve into a powerful yet overlooked feature dating back to 1992 - the XL4 macro function language - which remains surprisingly relevant in modern Excel.
Topics covered in this tutorial include:
1. Utilizing the "GET.CELL" function: We'll explore function number 63 within "GET.CELL," which retrieves cell fill color. This technique allows us to sum or count cells based on their color, offering a streamlined solution for data analysis tasks.
2. Creating named ranges: Learn how to leverage named ranges to incorporate macro formulas into your Excel workflow. Named ranges provide a workaround for using outdated functions within Excel's framework, enabling seamless integration of color-based calculations.
3. Implementing SUMIF and COUNTIF functions: I'll demonstrate how to use the SUMIF and COUNTIF functions in conjunction with named ranges to perform summation and counting operations based on cell color criteria. This actionable approach ensures accurate results while simplifying complex data analysis tasks.
4. Overcoming limitations: Discover strategies for overcoming limitations associated with macro functions, such as the need for manual recalculation and compatibility issues with conditional formatting. Learn how to force recalculation to ensure real-time updates and mitigate potential discrepancies.
5. Exploring advanced features: Unlock the full potential of macro functions by exploring additional features and applications beyond basic summing and counting. From customizing macros to integrating them into worksheets, discover a wealth of possibilities for optimizing your Excel workflow.
By the end of this tutorial, you'll have a comprehensive understanding of how to effectively leverage macro functions to analyze and manipulate data based on cell color in Excel. Whether you're a seasoned Excel user or new to the platform, this results-driven tutorial offers practical insights and actionable steps to enhance your productivity and proficiency in data analysis.
Don't miss out on the opportunity to explore the hidden potential of Excel's macro functions. Join me in uncovering new possibilities and expanding your Excel skill set. If you find this tutorial helpful, don't forget to give it a thumbs up, subscribe for more insightful content, and share your thoughts in the comments below. Your feedback and suggestions are invaluable as we continue to explore the depths of Excel's capabilities together.
Watch video Unveiling Excel's Hidden Gem: Sum and Count by Cell Color Made Easy online without registration, duration hours minute second in high quality. This video was added by user Up4Excel 11 February 2020, don't forget to share it with your friends and acquaintances, it has been viewed on our site 28,534 once and liked it 145 people.