Counting Highlighted Cells- Fastest way?

[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

 

[Bernie Deitrick]

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

 

[tjohnsox]

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 -

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.

 

[Bernie Deitrick]

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 -

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.

 

[tjohnsox]

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 -

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.