VideoPhoto

Showing posts with label count. Show all posts
Showing posts with label count. Show all posts

25 December, 2021

COLORS in Cells: How to Get, Set and Use them

It's really easy to color Excel cells, their background, content or borders. We can do it either directly (using Format Cells... or some Font options) or indirectly - using Conditional Formatting in a variety of available ways in the Excel menu.

When it comes to determining (getting) exact codes/names of colors being already used in a worksheet, it can be a bit complicated. And it may happen that you need to know what colors were originally used for background filling of some cells. Well, we can't find it out by using any Excel function or formula. So, practically, we must use macros (VBA codes) to solve such puzzles. E.g., the following code will determine Color Index for background color of cell A2.

Press ALT+F11 keys to display the VBA editing window and enter there this code:

Sub getCellBG()
Dim getC as Integer
getC=Range("A2").Interior.ColorIndex
End Sub

17 October, 2021

TOP TIP: Counting Cells Containing Numbers Meeting Specific Criteria

Most of the time we organize our data in orderly Excel tables making their analysis pretty straightforward. Sometimes, however, we may have to deal with chaotic sets of numbers located all over different ranges/worksheets, like in this somewhat bloated example:

How to count and analyse them? Couple of examples follow.

13 October, 2021

To COUNT any Characters or Strings in any Range of Cells

Use  an ARRAY formula. You can find out the number of occurrences of anything (e.g. BS, 2016, s, graph, three days, 50%, etc.) within a given cell or a range of cells.

Assuming that your data range is e.g. A2:D100, select any cell outside that range and type the formula similar to this one:

 =SUM((LEN(A2:D100)-LEN(SUBSTITUTE(A2:D100,"Fig.","")))/LEN("Fig."))

and simultaneously hold down the CTRL and the SHIFT keys as you press ENTER. The formula will show up in the formula bar, surrounded by curly brackets. And the cell where you array-entered the formula will show the result, like here in cell G1: