Need Help with Count by Cell Color and Content

**sjhoward60** · 04-11-2014, 02:10 PM

I'm trying to come up with a way to count cells with specific colors and names. I've attached a small subset of of the actual file. I've tried numerous combinations of Functions loaded into a module, and the one in the attached sheet is as close as I have come. Here's the script I found, along with the formula I'm using (it crosses worksheets):

Please Login or Register  to view this content.

=SUMPRODUCT(COUNTIF(CheckOff_Summer2014!$D$29:$Q$34,A4)*AND(Countcolour(CheckOff_Summer2014!$D$29:$Q$34,CheckOff_Summer2014!$O$1)))

Data is on worksheet CheckOff_Summer2014, and the formula is on worksheet Report. Data is using a List from the worksheet List to select names, and the colors have been applied manually (no conditional formatting). The colors are at the top of the data sheet, and they are labeled with their color indexes.

Problem Statement: I need to count cells from each date range that contain names and GREEN fill into the Basic columns for their specified dates, and cells with names and BLUE fill into the Advanced columns for their specified dates. The formula is not discrimating between colors... it is counting any and all fill colors into BOTH columns.

I'm a strong Excel user, but not a programmer. So while I can copy/paste script and work with the formulas, my skills are limited. Any help would be greatly appreciated.

Thanks,
Sheri

**jolivanes** · 04-11-2014, 03:28 PM

sjhoward60.
Please read and adhere to the forum rules. In your case rule #3, which states:
3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button). The result will appear like this in the post window: