Displaying a 1 in conditional formatting rules

[Stef9910]

Hello all,

I have received some fantastic help from awoohaw, PeteWright and fluff in recent days and one more problem to conquer.

Is it possible to add to the conditional format rules, =AND(N$62>=$B64,N$62<=$D64,ISNUMBER($B64)) to have the number 1 appear in the cells?

This is so I can total the number cells that display the number 1 throughout the spreadsheet.

Your help will be greatly received.

Thank you

Stefan

 

[Peter_SSs]

Is it possible to add to the conditional format rules, =AND(N$62>=$B64,N$62<=$D64,ISNUMBER($B64)) to have the number 1 appear in the cells?

This is so I can total the number cells that display the number 1 throughout the spreadsheet.

Getting back to your original question now that we have a bit more information: If you don't have other values/formulas in the X15 to BF157 range, then you could put this formula in X16 and copy it to other cells in that range.
Note that I have used @arthurbr's combined formula in my conditional formatting (and in the worksheet formulas producing the "1" values.

Stef9910.xlsm
	X	Y	Z	AA	AB	AC
15	1	1			1
16	1		1	1		1
17	1	1		1
18	1			1
19	1	1
20
21	1	1		1	1
22	1	1
23		1			1
24	1	1			1
25	1	1	1	1		1
Sheet1

Cell Formulas
Range	Formula
X15:AC25	X15	=IF(OR(AND(X$13>=$N15,X$13<=$P15),AND(X$13>=$L15,OR(X$13<=$N15,X$13<=$R15),ISNUMBER($L15))),1,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
X15:AC25	Expression	=AND(X$13>=$L15,OR(X$13<=$N15,X$13<=$R15),ISNUMBER($L15))	text	NO
X15:AC25	Expression	=AND(X$13>=$N15,X$13<=$P15)	text	NO

 

[awoohaw]

@Stef9910 ,
Just to add one more thing into all of this.
If you want to present the worksheet with the "1"s hidden you can also change the font in the conditional formatting to match the cell color. You would still be able to do arithmetic with the cells, but they would not be seen on the cell grid.

 

[Stef9910]

That's amazing and solves my problem, thank you so so much Peter_SSs, arthurbr and awoohaw

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

 

[Stef9910]

You're welcome. Glad we could help. Thanks for the follow-up.

Hi ya, it's the thick Aussie here again, found another problem!!

How would i make the formula you done, =IF(OR(AND(X$13>=$N15,X$13<=$P15),AND(X$13>=$L15,OR(X$13<=$N15,X$13<=$R15),ISNUMBER($L15))),1,"")
display a 1 for the green cells and a 2 for the red cells?

at the top of the spreadsheet i have one green cell with a 1 in it which i am using a referencing cell to count the 1's using =COUNTIF(X15:X109, AT3) AT3 being the green cell with a 1 in it.

The red cell at AT5 has a 2 in it, i would like to count the red cells with 2 in it.

Is it possible to make the above formula place a 1 in the green cells for X$13>=$N15,X$13<=$P15 part of the formula and a 2 for the X$13<=$N15,X$13<=$R15 part of the formula?

Thank you

 

[Peter_SSs]

How would i make the formula ...
display a 1 for the green cells and a 2 for the red cells?

Try

Stef9910.xlsm
	X	Y	Z	AA	AB	AC
15	1	1			1
16	1		2	1		2
17	2	2		2
18	2			2
19	2	2
20
21	1	1		1	1
22	2	2
23		2			2
24	2	2			1
25	2	2	2	2		2
Sheet3

Cell Formulas
Range	Formula
X15:AC25	X15	=IF(AND(X$13>=$N15,X$13<=$P15),1,IF(AND(X$13>=$L15,OR(X$13<=$N15,X$13<=$R15),ISNUMBER($L15)),2,""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
X15:AC25	Expression	=AND(X$13>=$L15,OR(X$13<=$N15,X$13<=$R15),ISNUMBER($L15))	text	NO
X15:AC25	Expression	=AND(X$13>=$N15,X$13<=$P15)	text	NO

 

[Stef9910]

Try

Stef9910.xlsm
	X	Y	Z	AA	AB	AC
15	1	1			1
16	1		2	1		2
17	2	2		2
18	2			2
19	2	2
20
21	1	1		1	1
22	2	2
23		2			2
24	2	2			1
25	2	2	2	2		2
Sheet3

Cell Formulas
Range	Formula
X15:AC25	X15	=IF(AND(X$13>=$N15,X$13<=$P15),1,IF(AND(X$13>=$L15,OR(X$13<=$N15,X$13<=$R15),ISNUMBER($L15)),2,""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
X15:AC25	Expression	=AND(X$13>=$L15,OR(X$13<=$N15,X$13<=$R15),ISNUMBER($L15))	text	NO
X15:AC25	Expression	=AND(X$13>=$N15,X$13<=$P15)	text	NO

Thankyou Peter_SSs,that worked brilliantly,

So I have one last thing to work out and then hopefully I am done!

From the highlighted cells that has been counted, I would like to count if they Scheduled or Unscheduled, would this be possible?

minisheet.xlsx
	B	C	D	E	F	G		I	J	K	L	M	N
4												1	Faciltated
5
6												2	Not Facilitated
7						April		1-Apr-23
8								1	2	3	4	5	6
9	Type	Status	Recieved Date	Start Date	End Date	RTP		S	S	M	T	W	T
10	Unscheduled	Completed	31/3/23	1/4/23	30/4/23			1	1	1	1	1	1
11	Unscheduled	Completed	29/3/23	30/3/23	20/4/23			1	1	1	1	1	1
12	Unscheduled	Completed	2/4/23	2/4/23	30/4/23				1	1	1	1	1
13	Unscheduled	Completed	2/4/23	2/4/23	4/4/23				1	1	1
14	Scheduled	Completed		3/4/23	4/4/23					1	1
15	Scheduled	Completed		4/4/23	4/4/23						1
16	Unscheduled	Completed	4/4/23	4/4/23	7/4/23						1	1	1
17	Scheduled	Completed		5/4/23	6/4/23							1	1
18	Unscheduled	Not Completed	5/4/23			5/4/23						2
19	Unscheduled	Not Completed	5/4/23			5/4/23						2
20	Unscheduled	Completed	5/4/23	7/4/23	30/4/23							2	2
21	Unscheduled	Completed	3/4/23	4/4/23	5/4/23					2	1	1
22	Unscheduled	Not Completed	5/4/23			10/4/23						2	2
23	Unscheduled	Not Completed	5/4/23			5/4/23						2
24	Unscheduled	Not Completed	6/4/23			6/4/23							2
25	Unscheduled	Not Completed	7/4/23			7/4/23
26	Unscheduled	Completed	8/4/23	8/4/23	10/4/23
27	Unscheduled	Not Completed	8/4/23			8/4/23
28	Unscheduled	Completed	9/4/23	10/4/23	12/4/23
29	Scheduled	Completed		11/4/23	11/4/23
30	Scheduled	Not Completed	6/4/23			6/4/23							2
31	Scheduled	Completed		11/4/23	11/4/23
32	Unscheduled	Completed	11/4/23			12/4/23
33	Scheduled	Completed		20/4/23	20/4/23
34	Unscheduled	Completed	14/4/23	14/4/23	2/5/23
35								2	4	5	8	6	Completed
36	7							0	0	1	0	5	Failed
37	18							0					Scheduled
38								0					Unscheduled
39
Hospital Sit Gantt Chart

Cell Formulas
Range	Formula
I7	I7	=I8
J8:N8	J8	=I8+1
I9:N9	J9	=LEFT(TEXT(J8,"ddd"),1)
I10:N34	J10	=IF(AND(J$8>=$E10,J$8<=$F10),1,IF(AND(J$8>=$D10,OR(J$8<=$E10,J$8<=$G10),ISNUMBER($D10)),2,""))
J35	J35	=COUNTIFS(J10:J34, Q4)
K35	K35	=COUNTIFS(K10:K34, Q4)
L35	L35	=COUNTIFS(L10:L34, Q4)
M35	M35	=COUNTIFS(M10:M34, Q4)
J36	J36	=COUNTIFS(J10:J34, Q6)
K36	K36	=COUNTIFS(K10:K34, Q6)
L36	L36	=COUNTIFS(L10:L34, Q6)
M36	M36	=COUNTIFS(M10:M34, Q6)
B36	B36	=COUNTIF(Table1[Type],"Scheduled")
B37	B37	=COUNTIF(Table1[Type],"Unscheduled")
I8	I8	=G7
I35	I35	=COUNTIFS(I10:I34, Q4)
I36	I36	=COUNTIF(I10:I34, Q6)
I37	I37	=COUNTIF(I10:I34, B36)
I38	I38	=COUNTIF(I10:I34, B37)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AO10:AT10,I10:AM10,I11:AQ34	Expression	=AND(I$8>=$E10,I$8<=$F10)	text	NO
AO10:AT10,I10:AM10,I11:AQ34	Expression	=AND(I$8>=$D10,OR(I$8<=$E10,I$8<=$G10),ISNUMBER($D10))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G7	List	=$AR$9:$AR$20

 

[Peter_SSs]

Try formulas like

=SUMPRODUCT((B10:B34="Scheduled")*(I10:N34=1))

 

[Stef9910]

Try formulas like

=SUMPRODUCT((B10:B34="Scheduled")*(I10:N34=1))

Peter_SSs you are a living legend, thank you very much.

 

[Peter_SSs]

Cheers. You're welcome.