Counting Highlighted Cells- Fastest way?

  • Thread starter Thread starter tjohnsox
  • Start date Start date
T

tjohnsox

Currently I am working on web reports in which I have highlighted
cells in several different worksheets.

I then have a summary page which counts the Sum of Column J,
COUNTA(J2:J1000) which counts the number of data entries in the range,
and then i have to manually filter column J by highlighted
color(yellow). Not all rows have a highlighted column and so I
currently just Filter by Cell Color and is the Count function at the
bottom of the screen; however this is time consuming and I am trying
to automate this report as much as I can, but I am stuck on this.

I am using Excel 2007 and have some experience with VBA, but cannot
seem to come up with a way to automatically count(through function or
macro) populate my summary page.

If anyone has any suggestions or ideas, I would greatly appreciate it.

Regards,
Tim
 
Tim,

Can you describe the logic that you use to select the cells that you
highlight? You could build that into a formula, and do the count
automatically - much less prone to error.

HTH,
Bernie
MS Excel MVP
 
Tim,

Can you describe the logic that you use to select the cells that you
highlight? You could build that into a formula, and do the count
automatically - much less prone to error.

HTH,
Bernie
MS Excel MVP










- Show quoted text -
Click to expand...

Every day there are a certain number of calls due per person based on
4 levels. Level 1 has to have at least 1 call every 30 days due the
15th of the month, level 2 having 2 calls, one every 15 days. Level 3
has 3 calls due, one every 9 days. Level 4 has 4 calls due, one every
7 days. Example, if there are 0 calls made month to date for a level
1 on the 15th, then that cell is highlighted. Or for a Level 3 with 0
calls made the 20th of the month therefore has 2 calls missing and is
highlighted as well.

This might be a bit confusing but hopefully it helps you help me
figure out how to count these highlighted cells.
 
Well, you don't describe your data table very well, but a formula like this:

=SUMPRODUCT(($A$2:$A$1000="Level 1")*($B$2:$B$1000=0))

would count the number of Level 1 (as noted in column A) which have zero calls (as noted in column
B).

You could also use a column of formulas like this (Where A2 has Level 1, 2, 3, 4, and B2 has the
number of calls...)

=IF(B2<VALUE(RIGHT(A2,1)),"Call Due","Call Not Due")

and then count the "Call Due" s

=COUNTIF(K:K,"Call Due")

HTH,
Bernie
MS Excel MVP


Tim,

Can you describe the logic that you use to select the cells that you
highlight? You could build that into a formula, and do the count
automatically - much less prone to error.

HTH,
Bernie
MS Excel MVP










- Show quoted text -
Click to expand...

Every day there are a certain number of calls due per person based on
4 levels. Level 1 has to have at least 1 call every 30 days due the
15th of the month, level 2 having 2 calls, one every 15 days. Level 3
has 3 calls due, one every 9 days. Level 4 has 4 calls due, one every
7 days. Example, if there are 0 calls made month to date for a level
1 on the 15th, then that cell is highlighted. Or for a Level 3 with 0
calls made the 20th of the month therefore has 2 calls missing and is
highlighted as well.

This might be a bit confusing but hopefully it helps you help me
figure out how to count these highlighted cells.
 
Well, you don't describe your data table very well, but a formula like this:

=SUMPRODUCT(($A$2:$A$1000="Level 1")*($B$2:$B$1000=0))

would count the number of Level 1 (as noted in column A) which have zero calls (as noted in column
B).

You could also use a column of formulas like this  (Where A2 has Level 1, 2, 3, 4, and B2 has the
number of calls...)

=IF(B2<VALUE(RIGHT(A2,1)),"Call Due","Call Not Due")

and then count the "Call Due" s

=COUNTIF(K:K,"Call Due")

HTH,
Bernie
MS Excel MVP








Every day there are a certain number of calls due per person based on
4 levels.  Level 1 has to have at least 1 call every 30 days due the
15th of the month, level 2 having 2 calls, one every 15 days.  Level 3
has 3 calls due, one every 9 days.  Level 4 has 4 calls due, one every
7 days.  Example, if there are 0 calls made month to date for a level
1 on the 15th, then that cell is highlighted. Or for a Level 3 with 0
calls made the 20th of the month therefore has 2 calls missing and is
highlighted as well.

This might be a bit confusing but hopefully it helps you help me
figure out how to count these highlighted cells.- Hide quoted text -

- Show quoted text -
Click to expand...

Hmm, I'll try my best to clarify:

Column I has the Current Level, either 1,2,3,4, or N/A.
Column J has the Number of Calls made MTD(Month to Date).

Does that help at all.
 
Back
Top