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]

Any ideas?

First: Check the formatting of the formula cell that is returning January and change it to Number (with 0 decimal places)

Second: If that does not resolve the problem, please give some simple sample data and explain the expected result in relation to that sample data. (With XL2BB you can hide any irrelevant columns/rows before producing your mini sheet to keep it a bit smaller/simpler)

 

[Stef9910]

First: Check the formatting of the formula cell that is returning January and change it to Number (with 0 decimal places)

Second: If that does not resolve the problem, please give some simple sample data and explain the expected result in relation to that sample data. (With XL2BB you can hide any irrelevant columns/rows before producing your mini sheet to keep it a bit smaller/simpler)

Thanks for your reply Peter_SSs,

The formula now results a 0 and not January, i was trying different formula's and inadvertently place it within a hidden part of the gantt chart.

Below is the minisheet example that i am attempting to get a result from for the count of the Metro Scheduled Completed and Metro Unscheduled Completed.

So for 1/4/23 from the gantt chart will result 2 for Unscheduled and 0 for Scheduled, or for 2/4/23 would result a 3 for Unscheduled and 1 for Scheduled.

Column E to S is a formal table

Hope this makes sense,

Book1
	E	F	G						M		O		Q		S						Y	Z	AA																				AU	AV															BK	BL	BM	BN	BO	BP	BQ
1																																																											Metro
2																																																											Regional
3																							Completed																				1																	Scheduled
4																																																												Unsceduled
5																							Failed																				2
6																																																												Metro Scheduled Completed					0
7																					1-Apr-23																																							Metro Unscheduled Completed					0
8																					1	2	3																				23	24
9	Region	Type	Status						Received Date		Start Date		End Date		RTP						S	S	M																				S	M
10	Metro	Unscheduled	Completed						1/4/23		1/4/23		30/4/23								1	1	1																				1	1
11	Metro	Unscheduled	Completed						1/4/23		1/4/23		20/4/23								1	1	1
12	Metro	Unscheduled	Completed						2/4/23		2/4/23		30/4/23									1	1																				1	1
13	Metro	Scheduled	Completed						2/4/23		2/4/23		4/4/23									1	1
14	Metro	Scheduled	Completed								3/4/23		4/4/23										1
15	Metro	Scheduled	Completed								4/4/23		4/4/23
16	Regional	Unscheduled	Completed						3/4/23		3/4/23		7/4/23										1
17	Metro	Scheduled	Completed								5/4/23		6/4/23
18	Metro	Unscheduled	Not Completed						2/4/23						2/4/23							2
19	Metro	Unscheduled	Not Completed						1/4/23						1/4/23						2
Gantt Chart

Cell Formulas
Range	Formula
Y7	Y7	=Y8
BQ6:BQ7	BQ6	=COUNTIFS(Table14[[Type]:[Type]],$BL3,Table14[[Region]:[Region]],$BK1,OFFSET(Table14[[Status]:[Status]],0,5+COLUMNS($Y:Y)),$AU$5)
Y8	Y8	=X6
Z8:AA8,AU8:AV8	Z8	=Y8+1
Y9:AA9,AU9:AV9	Y9	=LEFT(TEXT(Y8,"ddd"),1)
Y10:AA19,AU10:AV19	Y10	=IF(AND(Y$8>=$O10,Y$8<=$Q10),1,IF(AND(Y$8>=$M10,OR(Y$8<=$O10,Y$8<=$S10),ISNUMBER($M10)),2,""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Y10:BC195	Expression	=AND(Y$8>=$O10,Y$8<=$Q10)	text	NO
Y10:BC195	Expression	=AND(Y$8>=$M10,OR(Y$8<=$O10,Y$8<=$S10),ISNUMBER($M10))	text	NO

 

[Peter_SSs]

I am unable to determine what columns are part of the formal table and what are not. Can you please advise the first and last column of Table14?

With a copy of your workbook, can you also remove the conditional formatting and post the sample again so that I can see what cells have what numbers in them?
Also, for the sample data that you provide next time, please advise the expected results in BQ6 and BQ7.

 

[Stef9910]

I am unable to determine what columns are part of the formal table and what are not. Can you please advise the first and last column of Table14?

With a copy of your workbook, can you also remove the conditional formatting and post the sample again so that I can see what cells have what numbers in them?
Also, for the sample data that you provide next time, please advise the expected results in BQ6 and BQ7.

Thanks for your reply Peter_SSs,

A is the first and X is the last column of Table 14, but in the minisheet E is the first and S is the last column.

If counting say column Z for the 2/4/2023 the expected result for BQ6 is 3 and BQ7 is 1.

I am sorry for any confusion and really appreciate your help, thank you.

minisheet 15.5.23.xlsx
	E	F	G						M		O		Q		S						Y	Z	AA																				AU	AV															BK	BL	BM	BN	BO	BP	BQ	BR
1																																																											Metro
2																																																											Regional
3																							Completed																				1																	Scheduled
4																																																												Unsceduled
5																							Failed																				2
6																																																												Metro Scheduled Completed					0
7																					1-Apr-23																																							Metro Unscheduled Completed					0
8																					1	2	3																				23	24
9	Region	Type	Status						Received Date		Start Date		End Date		RTP						S	S	M																				S	M
10	Metro	Unscheduled	Completed						1/4/23		1/4/23		30/4/23								1	1	1																				1	1
11	Metro	Unscheduled	Completed						1/4/23		1/4/23		20/4/23								1	1	1
12	Metro	Unscheduled	Completed						2/4/23		2/4/23		30/4/23									1	1																				1	1
13	Metro	Scheduled	Completed						2/4/23		2/4/23		4/4/23									1	1
14	Metro	Scheduled	Completed								3/4/23		4/4/23										1
15	Metro	Scheduled	Completed								4/4/23		4/4/23
16	Regional	Unscheduled	Completed						3/4/23		3/4/23		7/4/23										1
17	Metro	Scheduled	Completed								5/4/23		6/4/23
18	Metro	Unscheduled	Not Completed						2/4/23						2/4/23							2
19	Metro	Unscheduled	Not Completed						1/4/23						1/4/23						2
Gantt Chart

Cell Formulas
Range	Formula
Y7	Y7	=Y8
BQ6:BQ7	BQ6	=COUNTIFS(Table14[[Type]:[Type]],$BL3,Table14[[Region]:[Region]],$BK1,OFFSET(Table14[[Status]:[Status]],0,5+COLUMNS($Y:Y)),$AU$5)
Y8	Y8	=X6
Z8:AA8,AU8:AV8	Z8	=Y8+1
Y9:AA9,AU9:AV9	Y9	=LEFT(TEXT(Y8,"ddd"),1)
Y10:AA19,AU10:AV19	Y10	=IF(AND(Y$8>=$O10,Y$8<=$Q10),1,IF(AND(Y$8>=$M10,OR(Y$8<=$O10,Y$8<=$S10),ISNUMBER($M10)),2,""))

 

[Peter_SSs]

A is the first and X is the last column of Table 14, but in the minisheet E is the first and S is the last column.

Thanks for that clarification & for the revised sample data.

If counting say column Z for the 2/4/2023 the expected result for BQ6 is 3

I do not understand that expected result.
My understanding is that BQ6 is trying to count rows for Metro Scheduled Completed with a 2 in the relevant column (Z)
There are no rows in the sample data column Z that meet those criteria so how can the count be 2?

Similarly there are no rows in column Z of the sample data where the criteria of Metro Unscheduled Completed and 2 are met so again the result to me should be 0, not 1.

Having said that, I can see a problem with how you have adapted my formula, but to try to advise a correction that works, we need to resolve the above problem(s).

 

[Stef9910]

Thanks for that clarification & for the revised sample data.


I do not understand that expected result.
My understanding is that BQ6 is trying to count rows for Metro Scheduled Completed with a 2 in the relevant column (Z)
There are no rows in the sample data column Z that meet those criteria so how can the count be 2?

Similarly there are no rows in column Z of the sample data where the criteria of Metro Unscheduled Completed and 2 are met so again the result to me should be 0, not 1.

Having said that, I can see a problem with how you have adapted my formula, but to try to advise a correction that works, we need to resolve the above problem(s).

Completed have 1, not 2, in column Z.

My Mistake sorry, BQ6 is Scheduled and expected to display 1, BQ7 is Unscheduled and expected to display 3

 

[Peter_SSs]

Completed have 1, not 2, in column Z.

OK. I was confused because your formulas in BQ6 & BQ7 in post #34, which have labels in column BL including "Completed", both refer to cell $AU$5 which contains a "2".

BTW, you have incorrect spelling in BL4. It should be "Unscheduled"

See if these BP6:BR7 formulas do what you want for those cells.

Stef9910.xlsm
	E	F	G																		Y	Z	AA																				AU	AV															BK	BL				BP	BQ	BR
1																																																											Metro
2																																																											Regional
3																							Completed																				1																	Scheduled
4																																																												Unscheduled
5																							Failed																				2
6																																																												Metro Scheduled Completed				0	1	2
7																					01-Apr-23																																							Metro Unscheduled Completed				2	3	3
8																					1	2	3																				1	2
9	Region	Type	Status																		S	S	M																				S	M
10	Metro	Unscheduled	Completed																		1	1	1
11	Metro	Unscheduled	Completed																		1	1	1
12	Metro	Unscheduled	Completed																			1	1
13	Metro	Scheduled	Completed																			1	1
14	Metro	Scheduled	Completed																				1
15	Metro	Scheduled	Completed
16	Regional	Unscheduled	Completed																				1
17	Metro	Scheduled	Completed
18	Metro	Unscheduled	Not Completed																			2
19	Metro	Unscheduled	Not Completed																		2
Sheet6

Cell Formulas
Range	Formula
BP6:BR7	BP6	=COUNTIFS(Table14[[Type]:[Type]],$BL3,Table14[[Region]:[Region]],$BK$1,OFFSET(Table14[[Type]:[Type]],0,COLUMN(Y:Y)-COLUMN(Table14[[Type]:[Type]])),$AU$3)
Y7	Y7	=Y8
Y8	Y8	=X6
Z8:AA8,AU8:AV8	Z8	=Y8+1
Y9:AA9,AU9:AV9	Y9	=LEFT(TEXT(Y8,"ddd"),1)
Y10:AA19,AU10:AV19	Y10	=IF(AND(Y$8>=$O10,Y$8<=$Q10),1,IF(AND(Y$8>=$M10,OR(Y$8<=$O10,Y$8<=$S10),ISNUMBER($M10)),2,""))

 

[Stef9910]

OK. I was confused because your formulas in BQ6 & BQ7 in post #34, which have labels in column BL including "Completed", both refer to cell $AU$5 which contains a "2".

BTW, you have incorrect spelling in BL4. It should be "Unscheduled"

See if these BP6:BR7 formulas do what you want for those cells.

Stef9910.xlsm
	E	F	G																		Y	Z	AA																				AU	AV															BK	BL				BP	BQ	BR
1																																																											Metro
2																																																											Regional
3																							Completed																				1																	Scheduled
4																																																												Unscheduled
5																							Failed																				2
6																																																												Metro Scheduled Completed				0	1	2
7																					01-Apr-23																																							Metro Unscheduled Completed				2	3	3
8																					1	2	3																				1	2
9	Region	Type	Status																		S	S	M																				S	M
10	Metro	Unscheduled	Completed																		1	1	1
11	Metro	Unscheduled	Completed																		1	1	1
12	Metro	Unscheduled	Completed																			1	1
13	Metro	Scheduled	Completed																			1	1
14	Metro	Scheduled	Completed																				1
15	Metro	Scheduled	Completed
16	Regional	Unscheduled	Completed																				1
17	Metro	Scheduled	Completed
18	Metro	Unscheduled	Not Completed																			2
19	Metro	Unscheduled	Not Completed																		2
Sheet6

Cell Formulas
Range	Formula
BP6:BR7	BP6	=COUNTIFS(Table14[[Type]:[Type]],$BL3,Table14[[Region]:[Region]],$BK$1,OFFSET(Table14[[Type]:[Type]],0,COLUMN(Y:Y)-COLUMN(Table14[[Type]:[Type]])),$AU$3)
Y7	Y7	=Y8
Y8	Y8	=X6
Z8:AA8,AU8:AV8	Z8	=Y8+1
Y9:AA9,AU9:AV9	Y9	=LEFT(TEXT(Y8,"ddd"),1)
Y10:AA19,AU10:AV19	Y10	=IF(AND(Y$8>=$O10,Y$8<=$Q10),1,IF(AND(Y$8>=$M10,OR(Y$8<=$O10,Y$8<=$S10),ISNUMBER($M10)),2,""))

Thank you so much Peter_SSs, works brilliantly.

I am learning so much,

Thank you your time and patience with me.

 

[Peter_SSs]

You're welcome. Glad we got there in the end.