counting non-blank cells

rebecca g

New Member
Joined
Jun 14, 2007
Messages
6
Hello,
I am using the COUNT A function that allows you to count non-blank cells. The cells that I am counting are highlighted in three different colors. I would actually like to count non-blank cells that are two of those three colors and not count the ones that are the third color. For example, count the yellow and blue ones, but not the red ones.

Is there a way to make my formula =COUNTA(A1:A10) do that?

Thanks,
Rebecca
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Only in excel 2007.

However, if those cells are colored VIA conditional formatting, the criteria in the conditional formatting could be used to accomplish that.
 

ageren

Board Regular
Joined
Jul 30, 2007
Messages
60
copy the code below in your macro module ...

sub count_the_colors ()

[b1] = 0
last = [a65000].end(3).row
for i = 1 to last
if range("A"& i).interior.colorindex = 6 or _
range("A" & i ).interior.colorindex = 11 then
[b1] = [b1] + 1
end if
next i

end sub
 

rebecca g

New Member
Joined
Jun 14, 2007
Messages
6
Jonmo1: I only have Excel 2003. But you do mention that it can be done using conditional formatting. How would I do that?

Ageren: I wish I could better understand the advice you gave. I dont have a macro module and never use code. Should I?
 

ageren

Board Regular
Joined
Jul 30, 2007
Messages
60
OK. let's start :)

open your worksheet > click tools > click macro > click visual basic editor > click insert (at the VB editor window) > click module > copy above codes into blank page written by me > press F5

you will see the result at B1 cell....
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
How are the cells colored, manually changing them or with Conditional Formatting?
 

rebecca g

New Member
Joined
Jun 14, 2007
Messages
6
right now I am manually changing them because they change throughout the spreadsheet since the spreadsheet is a schedule and a colored cell relates to being on call. It changes all the time. Do you think this is something I should/could do with conditional formatting?

I am still trying to work through ageren's macro. working to apply it to my spreadsheet. for the macro to work, do the cells need to be colored through conditional formatting?
 

ageren

Board Regular
Joined
Jul 30, 2007
Messages
60
for the macro to work, do the cells need to be colored through conditional formatting?

No. Actually, they do not have to be painted through conditional formatting.. Otherwise, macro code won't work...
 

Forum statistics

Threads
1,181,358
Messages
5,929,524
Members
436,676
Latest member
Mavri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top