Count number of cells that meet conditional format

[alexwm]

Hi all,

I have done a bit of looking into this and could not come up with the answer on my own.

A little background on my situation: I have a calendar contained in my spreadsheet that is set to conditionally format the color of each cell based on if there is a note for any particular date (e.g. July 4th (B4) has a note "Independence Day" --this note fulfills a conditional formatting rule and causes B4 to turn blue). Respectively, dates that do not have notes, do not have color.

My dilemma: I need to know how many dates have met my conditional formatting rule. Since the rule refers to a formula that contains a Defined Name, I was not able to replicate it in a COUNTIFS function. I was able to find a custom function that would count the cells with a manually determined color, but this did not recognize those cells that were colored due to conditional formatting.

Any programmers out there with the answer might just become my new best friend... but don't let that stop you from posting the solution

Thanks in advance,
Alex

[JBeaucaire]

You've already gone through the process of creating a defined name, you will probably have to create a new similar scenario to count the results.

I have no guess at what you did the first time, so am not going to guess at the next version. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix. Use BEFORE/AFTER sheets if that helps make it clearer.

[alexwm]

ExcelCalendarTemplate for Attendance - Copy - Copy.xlsm

I did not build this spreadsheet from the ground up. A lot of it was done before me, but I am tweaking it to suit my purpose. You'll see off to the side I have created buttons that are meant to go over each month (when i get any potential formatting/programming done) so that when you click them they will take you to the respective month's sheet.

Let me know if seeing the workbook itself makes my original post a little more clear. I want there to be a cell at the bottom that shows me a total number of the cells, within multiple ranges, that have notes (or, fit the conditional format rule).

[JBeaucaire]

I guess I don't see your question clearly enough. On the YEAR sheet is a list of dates/holidays. There are 30 of them. In the calendar itself, the days that match those 30 entries format themselves blues with conditional formatting.

But ALL of them will match, none of them are make-believe dates, they are all read dates. So 30 holidays = 30 blue cells.

What am I missing?

[alexwm]

Simply using a COUNTIF function on the number of cells that reflect an event on a corresponding date would be all well and good, IF there were no possibility of having more than one event per date (as one example). Another example of why this is not sufficient, would be that the purpose of this calendar will not primarily be to reflect the number of major holidays (a simple task because of uniformity), but to record employee attendance--or more appropriately, employee absences. Once this template is constructed, it will be used for in excess of 800 employees.

Hence the need for a self-calculating cell to tell me how many dates each person was absent.

I do appreciate your efforts in helping me and I hope this helps clear up my goal.

[JBeaucaire]

The "calendar" format is completely inappropriate for the task you have described. I have been chastised over the years by many an Excel consultant to remember that "Color is not data". That's a good lesson. Color is for human eyes, to provide visual context to a large amount of onscreen data. But Excel isn't a human and things it must evaluate itself must not be color-based.

So, the data for your employees and dates and absences and whatever will end up on a sheet in a "list" at some point. THAT is data. All the relevant data needs to be in that list for analysis and reporting.

With that, a proper design in my mind is:

1) A "data sheet" listing each tracked event. The fields could be:

Date, Employee ID, Employee Name, EventCode


2) A pivot table on a "report sheet" is linked to the data on the data sheet and provides a "count" of how many times each employee registers a specific event.

Or

2) The "report sheet" has a unique list of all employees and a column for each eventcode where the tally's occur. There are simple COUNTIF() or COUNTIFS() in each cell in this table that collects the results in realtime as items are added to the data sheet.

[alexwm]

I will experiment with that idea. Thank you for the tip, I will keep that in mind for future projects. I suppose I was in fact trying to bring "life" to Excel...explains why I was having such a difficult time.

As a Forum Moderator: I ask you, shall I mark this thread as "Solved" now or wait until I come up with my final product and post an example?

[JBeaucaire]

The redesign will ultimately lead to new "different" kinds of questions, then best to just start over in a new thread.

Yes, I think you can close this thread, your next questions would be in a new scenario and thus the workbook and details can be started anew.