Is there a standard formula for counting highlighted cells in Excel 2007?

Excel 2007
Excel 2007 workbooks
Microsoft Excel database
microsoft excel functions
Is there a formula for when you highlight some data cells, for use of simply counting rows, that Excel will give you the count of those cells as the answer on another sheet in the workbook- just by highlighting? But this is based on highlighting random cells in a column every day, never the same cells.

Software/Hardware used:
Windows 2007 Excel 2007 - PC

Answer Wiki

Thanks. We'll let you know when a new response is added.

U may write a macro for this:
Sub Macro1()
MsgBox ActiveCell.Interior.ColorIndex
End Sub


Enter the following data into cells D1 to D4: 11, twelve…. just for an example :

Enter the following data into cell D6: 16.

Note that we have intentionally left cell D5 blank.

Click on cell E1 – the location where the results will be displayed.

Click on the Formulas tab.

Choose More Functions > Statistical from the ribbon to open the function drop down list.

Click on COUNT in the list to bring up that function’s dialog box.

Drag select cells D1 to D6 on the spreadsheet to enter the range into the dialog box.

Click OK.

The answer 4 should appear in cell E1 since only four of the six cells in the range contain numbers.

Cell D2, which contains text, and cell D5, which is blank, are ignored by the COUNT function.

When you click on cell E1 the complete function =COUNT( D1 : D6 ) appears in the formula bar above the worksheet.

Discuss This Question: 5  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • SbElectric
    I had a similar situation in the past but I got over with a mostly manual operation. I am interested with the solution provided by Rechil. But I could not make it work. The macro did not provide what I am looking for. The Count operation is not what Buddyboy123 asked (at least that is my understanding). Could Rechil please provide more detail – you provide such good and detailed answer. I can use your concept to find solution to my situation. Buddyboy123 were you able to apply Rechil’s suggestion – just curious? I have a rather large Spreadsheet & would like to know the cell addresses when some one highlights it (by putting background color).
    2,540 pointsBadges:
  • Subhendu Sen
    There isn't a built in function for this, BUT you can create your own. For eg. if u want to highlight colored range.... Press Alt F11 to get into the VBA editor, then insert a module (insert menu) into your workbook. Into this write the following: I just provide as an example, u must change according to ur scenario..... Function colornumber(myvar As Range) colornumber = myvar.Interior.ColorIndex End Function Function colorcount(myvar As Range, ColVar As Long) For Each cell In myvar If cell.Interior.ColorIndex = ColVar Then colorcount = colorcount + 1 Next End Function These two functions give you what you need Enter this formula onto the worksheet: =colorcount(A1:A16,colornumber(A1)) and you will get the result you need
    140,480 pointsBadges:
  • Gshmn
    how to used standardize formula in excel
    10 pointsBadges:
  • Subhendu Sen
    @Gshmn, please create a new question under "Ask a Question" tag and if possible read the Tips on ur right hand side under the caption "Tips on asking a question". It helps to see ur Q maximum members and u can get maximum answers from this community. Please visit Here and create a new question. I will definitely revert back, if possible. Thanks--
    140,480 pointsBadges:
  • ExcelNoob
    I used Rechill's code and it worked beautifully, accept that when I highlighted somethign else it wouldn't automatically update. So I don't know how to fix that. However that was yesterday. Today i opened up the spreadsheet and the function no longer worked... I don't know much about VBA so I dont know what went wrong
    10 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: