Displaying a 1 in conditional formatting rules

S

Stef9910

Board Regular
Joined
Nov 2, 2022
Messages
72
Office Version
  1. 2010
Platform
  1. Windows
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
 
Sort by date Sort by votes
Stef9910 said:
Any ideas?
Click to expand...
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)
 
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Peter_SSs said:
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)
Click to expand...

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
EFGMOQSYZAAAUAVBKBLBMBNBOBPBQ
1Metro
2Regional
3Completed1Scheduled
4Unsceduled
5Failed2
6Metro Scheduled Completed0
71-Apr-23Metro Unscheduled Completed0
81232324
9RegionTypeStatusReceived DateStart DateEnd DateRTP SSMSM
10MetroUnscheduledCompleted1/4/231/4/2330/4/2311111
11MetroUnscheduledCompleted1/4/231/4/2320/4/23111  
12MetroUnscheduledCompleted2/4/232/4/2330/4/23 1111
13MetroScheduledCompleted2/4/232/4/234/4/23 11  
14MetroScheduledCompleted3/4/234/4/23  1  
15MetroScheduledCompleted4/4/234/4/23     
16RegionalUnscheduledCompleted3/4/233/4/237/4/23  1  
17MetroScheduledCompleted5/4/236/4/23     
18MetroUnscheduledNot Completed2/4/232/4/23 2   
19MetroUnscheduledNot Completed1/4/231/4/232    
Gantt Chart
Cell Formulas
RangeFormula
Y7Y7=Y8
BQ6:BQ7BQ6=COUNTIFS(Table14[[Type]:[Type]],$BL3,Table14[[Region]:[Region]],$BK1,OFFSET(Table14[[Status]:[Status]],0,5+COLUMNS($Y:Y)),$AU$5)
Y8Y8=X6
Z8:AA8,AU8:AV8Z8=Y8+1
Y9:AA9,AU9:AV9Y9=LEFT(TEXT(Y8,"ddd"),1)
Y10:AA19,AU10:AV19Y10=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
CellConditionCell FormatStop If True
Y10:BC195Expression=AND(Y$8>=$O10,Y$8<=$Q10)textNO
Y10:BC195Expression=AND(Y$8>=$M10,OR(Y$8<=$O10,Y$8<=$S10),ISNUMBER($M10))textNO
 
Upvote 0
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.
 
Upvote 0
Peter_SSs said:
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.
Click to expand...
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
EFGMOQSYZAAAUAVBKBLBMBNBOBPBQBR
1Metro
2Regional
3Completed1Scheduled
4Unsceduled
5Failed2
6Metro Scheduled Completed0
71-Apr-23Metro Unscheduled Completed0
81232324
9RegionTypeStatusReceived DateStart DateEnd DateRTP SSMSM
10MetroUnscheduledCompleted1/4/231/4/2330/4/2311111
11MetroUnscheduledCompleted1/4/231/4/2320/4/23111  
12MetroUnscheduledCompleted2/4/232/4/2330/4/23 1111
13MetroScheduledCompleted2/4/232/4/234/4/23 11  
14MetroScheduledCompleted3/4/234/4/23  1  
15MetroScheduledCompleted4/4/234/4/23     
16RegionalUnscheduledCompleted3/4/233/4/237/4/23  1  
17MetroScheduledCompleted5/4/236/4/23     
18MetroUnscheduledNot Completed2/4/232/4/23 2   
19MetroUnscheduledNot Completed1/4/231/4/232    
Gantt Chart
Cell Formulas
RangeFormula
Y7Y7=Y8
BQ6:BQ7BQ6=COUNTIFS(Table14[[Type]:[Type]],$BL3,Table14[[Region]:[Region]],$BK1,OFFSET(Table14[[Status]:[Status]],0,5+COLUMNS($Y:Y)),$AU$5)
Y8Y8=X6
Z8:AA8,AU8:AV8Z8=Y8+1
Y9:AA9,AU9:AV9Y9=LEFT(TEXT(Y8,"ddd"),1)
Y10:AA19,AU10:AV19Y10=IF(AND(Y$8>=$O10,Y$8<=$Q10),1,IF(AND(Y$8>=$M10,OR(Y$8<=$O10,Y$8<=$S10),ISNUMBER($M10)),2,""))
 
Upvote 0
Stef9910 said:
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.
Click to expand...
Thanks for that clarification & for the revised sample data.

Stef9910 said:
If counting say column Z for the 2/4/2023 the expected result for BQ6 is 3
Click to expand...
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).
 
Upvote 0
Peter_SSs said:
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).
Click to expand...
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
 
Upvote 0
Stef9910 said:
Completed have 1, not 2, in column Z.
Click to expand...
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
EFGYZAAAUAVBKBLBPBQBR
1Metro
2Regional
3Completed1Scheduled
4Unscheduled
5Failed2
6Metro Scheduled Completed012
701-Apr-23Metro Unscheduled Completed233
812312
9RegionTypeStatusSSMSM
10MetroUnscheduledCompleted111  
11MetroUnscheduledCompleted111  
12MetroUnscheduledCompleted 11  
13MetroScheduledCompleted 11  
14MetroScheduledCompleted  1  
15MetroScheduledCompleted     
16RegionalUnscheduledCompleted  1  
17MetroScheduledCompleted     
18MetroUnscheduledNot Completed 2   
19MetroUnscheduledNot Completed2    
Sheet6
Cell Formulas
RangeFormula
BP6:BR7BP6=COUNTIFS(Table14[[Type]:[Type]],$BL3,Table14[[Region]:[Region]],$BK$1,OFFSET(Table14[[Type]:[Type]],0,COLUMN(Y:Y)-COLUMN(Table14[[Type]:[Type]])),$AU$3)
Y7Y7=Y8
Y8Y8=X6
Z8:AA8,AU8:AV8Z8=Y8+1
Y9:AA9,AU9:AV9Y9=LEFT(TEXT(Y8,"ddd"),1)
Y10:AA19,AU10:AV19Y10=IF(AND(Y$8>=$O10,Y$8<=$Q10),1,IF(AND(Y$8>=$M10,OR(Y$8<=$O10,Y$8<=$S10),ISNUMBER($M10)),2,""))
 
Upvote 0
Peter_SSs said:
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
EFGYZAAAUAVBKBLBPBQBR
1Metro
2Regional
3Completed1Scheduled
4Unscheduled
5Failed2
6Metro Scheduled Completed012
701-Apr-23Metro Unscheduled Completed233
812312
9RegionTypeStatusSSMSM
10MetroUnscheduledCompleted111  
11MetroUnscheduledCompleted111  
12MetroUnscheduledCompleted 11  
13MetroScheduledCompleted 11  
14MetroScheduledCompleted  1  
15MetroScheduledCompleted     
16RegionalUnscheduledCompleted  1  
17MetroScheduledCompleted     
18MetroUnscheduledNot Completed 2   
19MetroUnscheduledNot Completed2    
Sheet6
Cell Formulas
RangeFormula
BP6:BR7BP6=COUNTIFS(Table14[[Type]:[Type]],$BL3,Table14[[Region]:[Region]],$BK$1,OFFSET(Table14[[Type]:[Type]],0,COLUMN(Y:Y)-COLUMN(Table14[[Type]:[Type]])),$AU$3)
Y7Y7=Y8
Y8Y8=X6
Z8:AA8,AU8:AV8Z8=Y8+1
Y9:AA9,AU9:AV9Y9=LEFT(TEXT(Y8,"ddd"),1)
Y10:AA19,AU10:AV19Y10=IF(AND(Y$8>=$O10,Y$8<=$Q10),1,IF(AND(Y$8>=$M10,OR(Y$8<=$O10,Y$8<=$S10),ISNUMBER($M10)),2,""))
Click to expand...

Thank you so much Peter_SSs, works brilliantly.

I am learning so much,

Thank you your time and patience with me.
 
Last edited by a moderator:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Conditional formatting for specific number of cells
Replies
9
Views
406
Fluff
Fluff
L
  • Solved
Conditional Formatting Date Ranges
2
Replies
13
Views
2K
Joe4
Joe4
F
  • Solved
Colour all blank cells in a row if one cell in row has text.
Replies
3
Views
427
Joe4
Joe4
J
  • Question Question
Multiple conditional formatting
Replies
6
Views
178
StephenCrump
S
K
  • Question Question
Conditional Formating - Dynamic "not between"
Replies
3
Views
559
dreid1011
dreid1011

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top