Count Questions

[FRED_SHEET]

I have a Microsoft Worksheet that I would like to create three cells with answers to the following questions for the range A1:AA41.

1. What function do I use to count the number of cells in this range?
2. What function do I use to count the number of cells in this range with a formula?
3. What function do I use to count the number of cells which have a Yellow fill color of (#FFFF00)?

Last but not least.

4. What do the numbers at the bottom of a highlighted range refer to - Avg Count Sum?

 

[JamesCanale]

MrExcelPlayground.xlsm
	A	B
42	Count of cells	1107
43	Have a formula	8
44	Count of yellow cells	2
45	whenever you have a range selected in excel, the status bar at the bottom will give information on the min, average, max, sum,… it's customizable - but only works on numbers
Sheet42

Cell Formulas
Range	Formula
B42	B42	=COUNTA(A1:AA41)+COUNTBLANK(A1:AA41)
B43	B43	=SUMPRODUCT(--ISFORMULA(A1:AA41))
B44	B44	=yellowbackcolor(A1:AA41)

You'll need a custom function for the back color:

Function yellowbackcolor(r As Range) As Integer
    Dim c As Integer
    c = 0

    For Each cell In r
        If cell.Interior.Color = 65535 Then c = c + 1
    Next cell
    yellowbackcolor = c
End Function

Just check the cell.interior.color number (65535) is right by making sure your color is right. FFFF aught to be 65535.

 

[FRED_SHEET]

Thank You the answers to questions 1and 2 worked just fine. The answer to question number 4 told me what the numbers stood for.

Your answer to question three is going to take some work on my part as I have very limited knowledge of Visual Basic.

I am also going to have find out where the color numbers you refer to come from. I have been using the alpha numeric code under custom colors to define the fill colors I use.

Again, Thank you for your help

 

[JamesCanale]

VBA isn't so bad - there is a ton of help here and google on how to get started. But in this case, you need to go to visual basic in excel (alt f11) and add a module to the spreadsheet in the Project viewer on the top left. Paste the code into the module (and save as an .xlsm). It should work just like the way it is.

In general, you might start by recording macros and editing. But it's a whole world of depth in VBA.

 

[FRED_SHEET]

VBA isn't so bad - there is a ton of help here and google on how to get started. But in this case, you need to go to visual basic in excel (alt f11) and add a module to the spreadsheet in the Project viewer on the top left. Paste the code into the module (and save as an .xlsm). It should work just like the way it is.

In general, you might start by recording macros and editing. But it's a whole world of depth in VBA.

James,

I was able to find information on the Visual Basic color codes.

Now I know the Visual Basic Color code numbers.

Thanks for pointing this information out to me.