Formula required for displaying a value based on conditional formatting

[GreenAmigo]

Hi. Noob here...

I have a spreadsheet for the lottery results which contains conditional formatting and changes the cell colour based on the values of other cells.

So, cells G5:K5 and M5:N5 (chosen numbers) will change to green if the value entered matches a value in cells M2:Q2 and S2:T2 (drawn numbers).

(I would add an image here but keep getting an error when uploading images - "407 [IOErrorEvent type="ioError" bubbles=false cancelable=false eventPhase=2 text="Error#2038"]")

What I need now is, if a range contains cells which meet the conditional format rule, display a value, which will differ depending on how many cells meet the conditional format rule.

I have a list of cells for the prize breakdown so, for example if G5:K5 contains 4 cells which meet the conitional format rule, I need the formula to display the prize value for four numbers from this list.

Does this make sense? Is it even possible? Any help will be greatly appreciated...

Thanks

GA

[dilipandey]

HI GA,

welcome to the forum.
Would be better if you could upload a sample workbook and mention your expected output. Thanks.

Regards,
DILIPandey
<click on below * if this helps>

[GreenAmigo]

Hi DILIPandey

I'll sort one and post it...

Thanks

GA

[GreenAmigo]

Here's the example spreadsheet with an explanation...

Thanks

GA

[dilipandey]

Okay... see the attached file and review it. thanks.

LottoResultsExample01.xls

Regards,
DILIPandey
<click on below * if this helps>