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

 

[Stef9910]

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

So that worked well with the formula i applied as =SUMPRODUCT((Table1[Type]="Scheduled")*(X14:X108=1))

If change it to X201 from X108 (there is no data in the corresponding cells yet, scheduled or unscheduled) and highlighted cells to count, i get the error N/A, I am guessing because there is no data to count.

Is there a way for the formula to disregard the empty cells and still count what is there, for example where it should count and display a 2 it displays N/A if you hover the mouse over the hashtag filled cell.

 

[Peter_SSs]

=SUMPRODUCT((Table1[Type]="Scheduled")*(X14:X108=1))

That is a different scenario:

• You are now dealing with a formal table structure, that was not apparent before.
• The range with 1's that you are now checking is a single column. That was not the case before.

I would probably be using a different (more efficient) formula for the count if that is the circumstance.

In any case I'm not sure I understand the issue well enough to test a suggestion. Can you make a smaller example (10-15 rows not 100) and post that with XL2BB and explain again in relation to that sample?

 

[Stef9910]

That is a different scenario:

• You are now dealing with a formal table structure, that was not apparent before.
• The range with 1's that you are now checking is a single column. That was not the case before.

I would probably be using a different (more efficient) formula for the count if that is the circumstance.

In any case I'm not sure I understand the issue well enough to test a suggestion. Can you make a smaller example (10-15 rows not 100) and post that with XL2BB and explain again in relation to that sample?

So sorry for the confusion, I confuse myself!

The count of the completed and failed from the colored cells works great and will count if additional rows are added.

The Scheduled and Unscheduled count needs to come from the Type column and match the dates above the colored cells, so a different sheet in the workbook can pull data for the date, ie 1/4/23.

So for the minisheet in this reply, scheduled would display 0, 0, 1, 2, 0 and unscheduled would display 1, 2, 1, 0, 0, this working off of either the received date or start date.

I would like the count for the scheduled and unscheduled to include rows that are added when more data is input.

The area that is displaying the count will be hidden off to the right hand side of the colored cells, as a referencing point for another sheet in the workbook for that to self populate.

I hope this all makes sense.

minisheet.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
4										Completed	1		0	2	3	3	0	Completed
5													1	1	2	3	2	Failed
6										Failed	2		#N/A					Scheduled
7						April	1-Apr-23						#N/A					Unscheduled
8							1	2	3	4	5	6	7	8	9	10	11	12	13
9	Type	Status	Recieved Date	Start Date	End Date	RTP	S	S	M	T	W	T	F	S	S	M	T	W	T
10	Unscheduled	Completed	1/4/23	2/4/23	4/4/23		2	1	1	1
11	Unscheduled	Completed	2/4/23	2/4/23	3/4/23			1	1
12	Unscheduled	Not Completed	3/4/23			5/4/23			2	2	2
13	Scheduled	Not Completed	4/4/23			4/4/23				2
14	Scheduled	Completed		3/4/23	4/4/23				1	1
15	Scheduled	Completed		4/4/23	4/4/23					1
16	Unscheduled	Not Completed	2/4/23			5/4/23		2	2	2	2
17
Gantt Chart

Cell Formulas
Range	Formula
O4	O4	=COUNTIFS(I10:I20, L4)
P4	P4	=COUNTIFS(J10:J20, L4)
Q4	Q4	=COUNTIFS(K10:K20, L4)
R4	R4	= COUNTIFS(L10:L20, L4)
O5	O5	=COUNTIFS(I10:I20, L6)
P5	P5	=COUNTIFS(J10:J20, L6)
Q5	Q5	=COUNTIFS(K10:K20, L6)
R5	R5	= COUNTIFS(L10:L20, L6)
H7	H7	=H8
N4	N4	=COUNTIFS(H10:H20, L4)
N5	N5	=COUNTIF(H10:H20, L6)
N6	N6	=SUMPRODUCT((Table1[Type]="Scheduled")*(H10:H16=1))
N7	N7	=SUMPRODUCT((Table1[Type]="Unscheduled")*(H10:H16=1))
H8	H8	=G7
I8:T8	I8	=H8+1
H9:T9	H9	=LEFT(TEXT(H8,"ddd"),1)
H10:L16	H10	=IF(AND(H$8>=$E10,H$8<=$F10),1,IF(AND(H$8>=$D10,OR(H$8<=$E10,H$8<=$G10),ISNUMBER($D10)),2,""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AN10:AS10,H10:AL10,H11:AP34	Expression	=AND(H$8>=$E10,H$8<=$F10)	text	NO
AN10:AS10,H10:AL10,H11:AP34	Expression	=AND(H$8>=$D10,OR(H$8<=$E10,H$8<=$G10),ISNUMBER($D10))	text	NO

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

 

[Peter_SSs]

Still a bit hard to see what is the table and what is not, but does this do what you want for 'Completed' counts of Scheduled and Unscheduled?

Stef9910.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
4										Completed	1
5
6										Failed	2		0	0	1	2	0	Scheduled
7						April	01-Apr-23						0	2	2	1	0	Unscheduled
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	1/4/23	2/4/23	4/4/23		2	1	1	1
11	Unscheduled	Completed	2/4/23	2/4/23	3/4/23			1	1
12	Unscheduled	Not Completed	3/4/23			5/4/23			2	2	2
13	Scheduled	Not Completed	4/4/23			4/4/23				2
14	Scheduled	Completed		3/4/23	4/4/23				1	1
15	Scheduled	Completed		4/4/23	4/4/23					1
16	Unscheduled	Not Completed	2/4/23			5/4/23		2	2	2	2
17
18
Sheet4

Cell Formulas
Range	Formula
N6:R7	N6	=COUNTIFS(Table1[[Type]:[Type]],$S6,OFFSET(Table1[[Type]:[Type]],0,5+COLUMNS($H:H)),$L$4)

 

[Stef9910]

Once again Peter_SSs, you have amazed me, thanks you so much,

I have been attempting it myself since posting on here and came up with,

=COUNTIFS(Table1[[#All], [Type]], "*Scheduled*", Table1[[#All],[Start Date]],AB13)
=COUNTIFS(Table1[[#All], [Type]], "*Unscheduled*", Table1[[#All],[COPP/Admission Date]], AB13)

The cell ranges are different due to working within the actual spreadsheet.

I will be using your formula, as i know it will work better.

Once again, thank you for your time and patience with me.

 

[Peter_SSs]

I have been attempting it myself since posting on here and came up with,

=COUNTIFS(Table1[[#All], [Type]], "*Scheduled*", Table1[[#All],[Start Date]],AB13)

That will not work because "Scheduled" is also found within "Unscheduled".
For example, if I have understood correctly, for this sample data the result should be zero, but it is returning 2 due to the issue I mentioned above.

Stef9910.xlsm
	B	C	D	E	F	G															V						AB
9	Type	Status	Recieved Date	Start Date	End Date	RTP
10	Unscheduled	Completed	1/4/23	2/4/23	4/4/23
11	Unscheduled	Completed	2/4/23	2/4/23	3/4/23																						2
12	Unscheduled	Not Completed	3/4/23			5/4/23
13	Scheduled	Not Completed	4/4/23			4/4/23																					2/04/2023
14	Scheduled	Completed		3/4/23	4/4/23
15	Scheduled	Completed		4/4/23	4/4/23
16	Unscheduled	Not Completed	2/4/23			5/4/23
17
Sheet4

Cell Formulas
Range	Formula
AB11	AB11	=COUNTIFS(Table1[[#All], [Type]], "*Scheduled*", Table1[[#All],[Start Date]],AB13)

 

[Stef9910]

Magic, thank you, confirmed, I will go with your formula.

Thanks

 

[Peter_SSs]

 

[Stef9910]

So sorry to bother again, how would i add an addition search criteria to =COUNTIFS(Table1[[Type]:[Type]],$S6,OFFSET(Table1[[Type]:[Type]],0,5+COLUMNS($H:H)),$L$4)

Table1[[Region]:[Region]] with the word Metro that is located in $T6 this is if it was added to the above minisheet.

The formula from my spreadsheet is

=COUNTIFS(Table1[[Type]:[Type]],$BL3,Table1[[Region]:[Region]],$BK1,OFFSET(Table1[[Type]:[Type]],0,5+COLUMNS($Y:Y)),$AU$3)

$BL3 is where the word Scheduled is located
$BK1 is where the word Metro is located
$AU$3 is where the 1 is located for completed

I have checked all the data for incorrect dates and spellings and all is correct, but the above formula i have used returns the word January.

Any ideas?

Your help is always very appreciated, thank you.

 

[Stef9910]

So sorry to bother again, how would i add an addition search criteria to =COUNTIFS(Table1[[Type]:[Type]],$S6,OFFSET(Table1[[Type]:[Type]],0,5+COLUMNS($H:H)),$L$4)

Table1[[Region]:[Region]] with the word Metro that is located in $T6 this is if it was added to the above minisheet.

The formula from my spreadsheet is

=COUNTIFS(Table1[[Type]:[Type]],$BL3,Table1[[Region]:[Region]],$BK1,OFFSET(Table1[[Type]:[Type]],0,5+COLUMNS($Y:Y)),$AU$3)

$BL3 is where the word Scheduled is located
$BK1 is where the word Metro is located
$AU$3 is where the 1 is located for completed

I have checked all the data for incorrect dates and spellings and all is correct, but the above formula i have used returns the word January.

Any ideas?

Your help is always very appreciated, thank you.

=COUNTIFS(Table1[[Type]:[Type]],$BL3,Table1[[Region]:[Region]],$BK1,OFFSET(Table1[[Type]:[Type]],0,5+COLUMNS($Y:Y)),$AU$3) is no longer returning the word January but is returning a 0 when it should count 2

I had accidentally put the formula in a hidden part of the conditional formatted gantt chart.