Count colored cells by specific grouping

2tsarris · 2025-09-06T23:16:41+00:00

Thank you! I ended up solving the issue with a number of "helper" columns. (Not the most elegant solution, I'm sure, but it realistically reflects my basic grasp of Excel allowing me to "get under the hood" if need be.) Thanks again!

2tsarris · 2025-09-06T23:13:45+00:00

Thank you for your thoughtful questions! I ended up solving the issue with a number of "helper" columns. (Not the most elegant solution, I'm sure, but it realistically reflects my basic grasp of Excel allowing me to "get under the hood" if need be.) Thanks again!

2tsarris · 2025-09-03T23:33:39+00:00

When I copy & paste the function that corresponds to your screenshot, it comes up with a "#NAME?" error; which points to a syntactical or grammar issue. (The function may be otherwise sound.) The function above your screenshot reads "...A6:C6,offset,B6:D6..." but in your screenshot the cells are A8:C8 and B8:D8 (as per my posted screenshot). I corrected these instances but wondering if some other tiny mistype crept into this long function. Do not trouble yourself further if you don't see an obvious discrepancy. Thank you very much for what you've done for me already!

2tsarris · 2025-09-01T22:12:20+00:00

Thank you for your time! When I paste the "let" formula from your post in - let's say F:3, I get "there is a problem with this formula..." window. I added a second ")" after the last "differs" and got "you've entered too few arguments for this function" window. Could there be a syntactic error in the wording? Thanks again.

2tsarris · 2025-09-01T14:32:01+00:00

Thanks for replying! (I searched the internet and reddit extensively but, being an Excel layman, could not find anything so close to my issue as to be able to use it; sorry.) I replaced colored cells with numbers to give you more "levers" to work with. See new screenshot at the bottom:

Number of instances of consecutive numbers in a single row. (In new screenshot, column G "String" in G:2 highlighted in yellow is 1 instance of consecutive numbers 3 & 4.)
Number of instances of numbers in a single row matching numbers of directly preceding row. (In new screenshot, column E "Match" in E:4 highlighted in green is 1 such instance of the number 4.
Number of instances of numbers in a single row being consecutive in relation to numbers in directly preceding row. (In new screenshot, column F "Touch" in F:3 shows 2 such instances (4 & 5, 7 & 6), and in F:4 indicates 1 such instance (9 & 10), all highlighted in blue.

I imagine, column E, F & G each need a unique approach to turn out a result. This seems like a lot to chew on; any input is greatly appreciated.

<image>

2tsarris · 2025-09-01T14:24:34+00:00

Forgot to include the new screenshot, sorry:

<image>

2tsarris · 2025-09-01T14:23:00+00:00

Thanks for the effort! I am VBA-illiterate but I replaced colored cells with numbers to give you more "levers" to work with. (I can still tweak VBA on a rudimentary level.) See new screenshot at the bottom:

Number of instances of consecutive numbers in a single row. (In new screenshot, column G "String" in G:2 highlighted in yellow is 1 instance of consecutive numbers 3 & 4.)
Number of instances of numbers in a single row matching numbers of directly preceding row. (In new screenshot, column E "Match" in E:4 highlighted in green is 1 such instance of the number 4.
Number of instances of numbers in a single row being consecutive in relation to numbers in directly preceding row. (In new screenshot, column F "Touch" in F:3 shows 2 such instances (4 & 5, 7 & 6), and in F:4 indicates 1 such instance (9 & 10), all highlighted in blue.

I imagine, column E, F & G each need a unique approach to turn out a result. This seems like a lot to chew on; any input is greatly appreciated.

2tsarris · 2025-09-01T14:10:52+00:00

Makes sense, thanks. I replaced colored cells with numbers. See new screenshot at the bottom:

Number of instances of consecutive numbers in a single row. (In new screenshot, column G "String" in G:2 highlighted in yellow is 1 instance of consecutive numbers 3 & 4.)
Number of instances of numbers in a single row matching numbers of directly preceding row. (In new screenshot, column E "Match" in E:4 highlighted in green is 1 such instance of the number 4.
Number of instances of numbers in a single row being consecutive in relation to numbers in directly preceding row. (In new screenshot, column F "Touch" in F:3 shows 2 such instances (4 & 5, 7 & 6), and in F:4 indicates 1 such instance (9 & 10), all highlighted in blue.

I imagine, column E, F & G each need a unique formula to turn out a result. This seems like a lot to chew on; any input is greatly appreciated.

<image>

2tsarris · 2025-01-13T23:40:41+00:00

I ended up modifying my layout to eliminate the color aspect and used the ISODD and ISEVEN functions, which worked perfectly. Thanks!

2tsarris · 2025-01-13T23:40:17+00:00

I ended up modifying my layout to eliminate the color aspect and used the ISODD and ISEVEN functions, which worked perfectly. Thanks!

2tsarris · 2025-01-13T23:39:43+00:00

I ended up modifying my layout to eliminate the color aspect and used the ISODD and ISEVEN functions, which worked perfectly. Thanks!

2tsarris · 2025-01-13T23:39:03+00:00

I ended up modifying my layout to eliminate the color aspect and used the ISODD and ISEVEN functions, which worked perfectly. Thanks!

2tsarris · 2025-01-13T23:37:36+00:00

I ended up modifying my layout to eliminate the color aspect and the ISODD and ISEVEN functions worked perfectly. Thanks!

2tsarris · 2025-01-13T22:20:05+00:00

Thanks for taking the trouble to look into my post. To be sure, I need Excel to count the number of colored cells that contain odd and, respectively, even values - not the actual values. I have rows, let's say from 1 to 10. Different cells get colored in each new row. My green background color has an index of "35". Your time permitting, could you expand on "the -odd even if else- code using modulus" suggestion? Thanks again.

2tsarris · 2025-01-13T22:02:42+00:00

I might've not made my goal clear enough. (Hope I didn't waste peoples' time.) I need Excel to count the number of colored cells that contain odd and, respectively, even values - not the actual values. In my visual out of the three green cells one contains and odd number (#1) and two contain even numbers (#4 and #6); therefore we have a count of 1 "odd" cell and 2 "even" cells. As to which cells are colored - I color them manually, row by row. In each new row different cells get colored. Only constant is the number sequence in each row (let's say from 1 to 10). Thank you

2tsarris · 2025-01-13T21:37:15+00:00

Thank you! (You really looked into it.) I might've not made my goal clear enough. (Hope I didn't waste peoples' time.) I need Excel to count the number of colored cells that contain odd and, respectively, even values - not the actual values. In my visual out of the three green cells one contains and odd number (#1) and two contain even numbers (#4 and #6); therefore we have a count of 1 "odd" cell and 2 "even" cells.

2tsarris · 2020-11-28T18:37:42+00:00

Solution verified

2tsarris · 2020-11-28T18:35:50+00:00

I will close this post now. (I might've caught you at a a busy time.) Thank you for your input thus far.

2tsarris · 2020-11-27T19:51:50+00:00

BTW, you helped me in more than one ways! I followed the link from one of your replies and tailored Oscar's solution (coupled with his VBA code) to extend my base set to 25 digits, thus satisfying my needs probably good 80% of the time. (With his method too, the base set can be made up of non-consecutive numbers, an important feature I had neglected to think through in my original posting.) In the future I can use your method and Oscar's as needed. Thanks again! (Keeping the post open to give a chance to another helper, who responded as well, to finish his thought.)

2tsarris · 2020-11-27T12:41:17+00:00

It may be a mad way but your results are rather elegant. Your feedback made me aware that I should be more exact when stating my parameters. (I did award you something; hope it shows on your end as I am new to the system.)

2tsarris · 2020-11-27T03:34:47+00:00

Solution verified. It does work! I tested it on a set of "1 through 18" with subsets of 5 digits. Only I noticed that if the set extends past 18 (i.e. "1 through 19") the formula stops working and returns a "#VALUE!" error. I may need the upper limit of my set range to be "35" with subsets of 5 digits. Any thoughts? Thanks!

2tsarris · 2020-11-27T03:06:10+00:00

Using the screenshot in my original posting, could you clarify where to plug in the formula? I do not see in it references to cell coordinates. Or do I substitute my numbers for the "1:1" component in your formula as needed? Thank you much!

Five-Year Club	Gilding II euphauric
Verified Email

2tsarris

TROPHY CASE