Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

how to set up rows to count different coloured cells

0

i have a spread sheet for a lottery syndicate by entering the winning numbers and suplementry numbers the numbers in the rows change colour Each row is a seperate game and i have about 280 rows With the weekly drawn numbers the cell turns  green and if the game contains those numbers  and blue for the suplementry numbers Im looking for a way to auto count the blue cells and seperatly count the green cells in iach seperate row and place the tanny into the matching coulered cells in column K & L is it possible to come up with a solution to achieve this outcome Ihave included a dropbox copy of the sheet below

https://www.dropbox.com/scl/fi/lfp78z01qrstolvp23d0s/work-in-progress.xlsx?rlkey=jhlvy03wjyhdgirkpe75u3hpn&dl=0

excel
MS Office Version: 365 2022
posted by: grahamscown (rep: 8) on Sat Mar 23, 2024 at 6:06 pm
Answer
Discuss

Discussion

Hi Graham. I'll try to look at this tomorrow (my time) but what do you mean by "tanny"? Is it the count of the same coloured cells?
John_Ru (rep: 6142) Mar 23, '24 at 7:00 pm
sorry yes thats it the amount of blue cells into blue column in each row and same with the green 
grahamscown (rep: 8) Mar 23, '24 at 7:10 pm
@Graham,

What version of Excel are you using? I use 2016 and have tried about a dozen differnet formulas but nothing works. The formulas must only work in later editions so I can't test/verify them. Would you be open to using a VBA solution?

Cheers   :-)
WillieD24 (rep: 557) Mar 24, '24 at 12:55 am
@Willie- the question shows "365 2022"

@Graham - please see my Answer. It also worked well with Excel 2010 
John_Ru (rep: 6142) Mar 24, '24 at 3:56 am
@John
Thanks for that; I overlooked it when I read the post.
Every option I found didn't mention that it only worked in 365. It's too bad that other Excel resources don't include that important information the why Don always does.
WillieD24 (rep: 557) Mar 24, '24 at 8:14 am
@Willie - my Answer file should work with your 2016 version since it uses very old functions. If I use one of tbe newer functions, I generally check with the Microsoft support site e.g.
SEQUENCE function

Just under the title you'll see the start of a note which expands to show which versions it works with:
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel for iPad Excel for iPhone Excel for Android tablets Excel for Android phones
John_Ru (rep: 6142) Mar 24, '24 at 8:41 am
@John
Thanks for the tip and link; hopefully I will remember to check there when in doubt.
And Yes, your file with the array (CSE) formulas works with 2016.

Thanks.
WillieD24 (rep: 557) Mar 24, '24 at 9:39 pm
Add to Discussion

Answers

0
Selected Answer

Graham

Your "correct" cells in columns C:H are green and blue from conditional formatting rather than fill (so can't be summed by a formula related to the seen colour).

I see you use Excel 365 so to count the number of correct (conditionally formatted green) cells, I put this in cell K2 of the attached revised file:

=SUM(COUNTIF(C2:H2,$O$4:$T$4))

and copied it down the column.

Note that  

  1. the range in bold "fixes" the cells containing regular lottery result numbers
  2. the Countif portion is an array fomula (which works fine in your Excel 365) as is. Users of other versions can open the attached file but may need to enter theer versions of the formula using Ctrl+Shift and Enter..

Similarly for the correcly guessed supplementary numbers (blue conditionally-formatted green) cells, I put this in cell L2:

=SUM(COUNTIF(C2:H2, $U$4:$V$4))

and copied down.

Hope this fixes your problem. If so, please be sure to mark this Answer as Selected.

posted by: John_Ru (rep: 6142) on Sun Mar 24, 2024 at 3:54 am
Discuss

Discussion

Thankyou John the solution you put forward works perfectly thanks for your help
grahamscown (rep: 8) Mar 24, '24 at 5:49 pm
Glad that worked for you. Thanks for selecting my Answer Graham. 
John_Ru (rep: 6142) Mar 24, '24 at 6:11 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login