Underline the last of each cell with conditional formatting

D

DarkoDeign2

Board Regular
Joined
Jun 20, 2023
Messages
76
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have a column $AI2:$AI1500 in this column I have ISO-week numbers (e.g. wk22)
In the next column $AJ2:$AJ1500 I have days of the week (e.g. Monday... Sunday). It can be multiple rows with the same days.

I want to a conditional format the last Sunday in every week in column AI.
What suggestions do you have for such a formula?
1689351291738.png
 

Attachments

  • 1689351181610.png
    1689351181610.png
    7.4 KB · Views: 7
Sort by date Sort by votes
Hi,
Thanks for the answer, but I want to have the total for each week. So if it's week 29, I want to have the sum of all values in Column AR that are "week 29" in column AK.
With this I get a sum of all the values in Column AR.
I hope you understand what I mean..
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
:oops: Didn't I just have incorrect sample data (I used my wrong sample sheet with the original CF)?
The formula did give the total for the week, it just wasn't in the last row of the week because of my dodgy data.


The highlighted Sunday supposed to be the last day of that week shouldn't it?
If so, same formula, better sample data.

DarkoDeign2.xlsm
AKALARAT
1Week noDays
2wk28Friday1 
3wk28Friday2 
4wk28Saturday3 
5wk28Sunday410
6wk29Sunday5 
7wk29Sunday6 
8wk29Sunday718
9wk30Monday8 
10wk30Monday9 
11wk30Tuesday10 
12wk30Tuesday11 
13wk30Friday12 
14wk30Saturday13 
15wk30Saturday14 
16wk30Sunday15 
17wk30Sunday16 
18wk30Sunday17125
19wk31Monday18 
20wk31Monday19 
21wk31Monday20 
22wk31Thursday21 
23wk31Friday22 
24wk31Saturday23 
25wk31Sunday24 
26wk31Sunday25 
27wk31Sunday26198
28wk32Monday27 
29wk32Tuesday28 
30wk32Wednesday29 
CF Last (2)
Cell Formulas
RangeFormula
AT2:AT30AT2=IF(AND($AL2="Sunday",$AK3&$AL3<>$AK2&$AL2),SUMIF($AK$2:$AK$1500,$AK2,$AR$2:$AR$1500),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AK2:AL30Expression=AND($AL2="Sunday",$AK3&$AL3<>$AK2&$AL2)textNO
 
Upvote 0
Hi again,

Unfortunately the result is the same.. But I have realized that I have asked for the wrong thing.

What I want to have in Column "AT" is the count of rows/per week based on following criterias:

Range1 = $F$2:$F$1500, Range criteria1 = "Global"
Range2 = $AT$2:$AT$1500, Range criteria2 = "wk22"
And this should be on the same row in colum AT as last Sunday in every week (Sundays in red font color)

So basically it would be something similar as the suggested in formula above with SUMIF, but instead I guess it would be "Countif" since we want to know how many rows and not the sum of the data in the rows/cells.

I pasted a screenshot of how I would imagine this to look in the final file.
Would appreciate your help with this.


1689862782074.png
 
Upvote 0
Too much manual typing to set that up to test. Could we have the sample data (and expected results filled in manually) with XL2BB? Hide any irrelevant columns before creating the mini sheet.
 
Upvote 0
It works, but you need to select the range that you want to show after you have hidden irrelevant columns and before you click 'Mini Sheet'
 
Upvote 0
So on the rows with red font, those indicate the last Sunday for each week. This is accomplished with formula:
=AND($AL2="Sunday",$AK3&$AL3<>$AK2&$AL2)
But on the same row as the last Sundays but in column AT I want to know how many "Global" I had per week.
The same with Column AU here I want to know how many "Local" I had per week.


Book1.xlsm
AFAGAIAJAKALAMANAOAPAQARASATAU
1Date receivedGlobal LocalStatusClose dateInv DurationWeek noDaysOngoingClosedStopGlobalLocalTotal (Acc)Global per weekLocal per week
22023-05-31GlobalCLOSED2023-07-2132dayswk22Wednesday010101
32023-06-04GlobalONGOINGwk22Sunday110202
42023-06-04GlobalCLOSED2023-07-217dayswk22Sunday12030330
52023-06-05LocalCLOSED2023-07-212dayswk23Monday130314
62023-06-05LocalCLOSED2023-07-213dayswk23Monday140325
72023-06-06GlobalCLOSED2023-07-2115dayswk23Tuesday150426
82023-06-06GlobalCLOSED2023-07-2132dayswk23Tuesday160527
92023-06-07LocalCLOSED2023-07-212dayswk23Wednesday170538
102023-06-07LocalCLOSED2023-07-212dayswk23Wednesday180549
112023-06-08LocalCLOSED2023-07-211dayswk23Thursday1905510
122023-06-08LocalCLOSED2023-07-211dayswk23Thursday11005611
132023-06-08LocalCLOSED2023-07-212dayswk23Thursday11105712
142023-06-08GlobalONGOINGwk23Thursday21106713
152023-06-09GlobalCLOSED2023-07-2123dayswk23Friday21207714
162023-06-09GlobalONGOINGwk23Friday31208715
172023-06-10LocalCLOSED2023-07-211dayswk23Saturday31308816
182023-06-10LocalCLOSED2023-07-211dayswk23Saturday31408917
192023-06-10LocalCLOSED2023-07-211dayswk23Saturday315081018
202023-06-11LocalCLOSED2023-07-211dayswk23Sunday316081119
212023-06-11LocalCLOSED2023-07-211dayswk23Sunday317081220512
222023-06-12GlobalCLOSED2023-07-211dayswk24Monday318091221
232023-06-12LocalCLOSED2023-07-211dayswk24Monday319091322
242023-06-13LocalCLOSED2023-07-212dayswk24Tuesday320091423
252023-06-13GlobalCLOSED2023-07-2123dayswk24Tuesday3210101424
262023-06-13LocalONGOINGwk24Tuesday4210101525
272023-06-14GlobalONGOINGwk24Wednesday5210111526
282023-06-15GlobalCLOSED2023-07-2122dayswk24Thursday5220121527
292023-06-13LocalCLOSED2023-07-214dayswk24Tuesday5230121628
302023-06-15LocalCLOSED2023-07-211dayswk24Thursday5240121729
312023-06-16LocalCLOSED2023-07-211dayswk24Friday5250121830
322023-06-17GlobalCLOSED2023-07-2125dayswk24Saturday5260131831
332023-06-17GlobalCLOSED2023-07-21dayswk24Saturday5270141832
342023-06-18GlobalCLOSED2023-07-216dayswk24Sunday528015183376
352023-06-19GlobalCLOSED2023-07-211dayswk25Monday5290161834
362023-06-19GlobalCLOSED2023-07-2112dayswk25Monday5300171835
372023-06-19LocalCLOSED2023-07-212dayswk25Monday5310171936
382023-06-19GlobalCLOSED2023-07-2125dayswk25Monday5320181937
392023-06-20LocalCLOSED2023-07-211dayswk25Tuesday5330182038
402023-06-20GlobalCLOSED2023-07-2120dayswk25Tuesday5340192039
412023-06-20GlobalCLOSED2023-07-2116dayswk25Tuesday5350202040
422023-06-20LocalCLOSED2023-07-212dayswk25Tuesday5360202141
432023-06-20LocalCLOSED2023-07-212dayswk25Tuesday5370202242
442023-06-21LocalCLOSED2023-07-212dayswk25Wednesday5380202343
452023-06-21GlobalCLOSED2023-07-2119dayswk25Wednesday5390212344
462023-06-22GlobalONGOINGwk25Thursday6390222345
472023-06-22LocalCLOSED2023-07-211dayswk25Thursday6400222446
482023-06-22LocalCLOSED2023-07-212dayswk25Thursday6410222547
492023-06-23LocalCLOSED2023-07-211dayswk25Friday6420222648
502023-06-23GlobalCLOSED2023-07-212dayswk25Friday6430232649
512023-06-25GlobalONGOINGwk25Sunday743024265098
Tracking
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AI2:AR1500Expression=AND($AL2="Sunday";$AK3&$AL3<>$AK2&$AL2)textNO
AP2:AR1500Expression=AND(ISBLANK($A3); NOT(ISBLANK($AG2)))textNO
AM2:AO1500Expression=AND(ISBLANK($A3); NOT(ISBLANK($AG2)))textNO
A2:AG1500Expression=$AG2="Closed"textNO
A2:AH1500Expression=$AG2="SALES STOP"textNO
A2:AG1500Expression=$AG2="Ongoing"textNO
Cells with Data Validation
CellAllowCriteria
AG2:AG51List=Status
 
Last edited:
Upvote 0
Thanks. Try this. (Note that more columns can be hidden as they are unrelated to the issue being asked. :))

DarkoDeign2.xlsm
FAKALATAU
1Global LocalWeek noDaysGlobal per weekLocal per week
2Global22Wednesday  
3Global22Sunday  
4Global22Sunday30
5Local23Monday  
6Local23Monday  
7Global23Tuesday  
8Global23Tuesday  
9Local23Wednesday  
10Local23Wednesday  
11Local23Thursday  
12Local23Thursday  
13Local23Thursday  
14Global23Thursday  
15Global23Friday  
16Global23Friday  
17Local23Saturday  
18Local23Saturday  
19Local23Saturday  
20Local23Sunday  
21Local23Sunday512
22Global24Monday  
23Local24Monday  
24Local24Tuesday  
25Global24Tuesday  
26Local24Tuesday  
27Global24Wednesday  
28Global24Thursday  
29Local24Tuesday  
30Local24Thursday  
31Local24Friday  
32Global24Saturday  
33Global24Saturday  
34Global24Sunday76
35Global25Monday  
36Global25Monday  
37Local25Monday  
38Global25Monday  
39Local25Tuesday  
40Global25Tuesday  
41Global25Tuesday  
42Local25Tuesday  
43Local25Tuesday  
44Local25Wednesday  
45Global25Wednesday  
46Global25Thursday  
47Local25Thursday  
48Local25Thursday  
49Local25Friday  
50Global25Friday  
51Global25Sunday98
Sheet1
Cell Formulas
RangeFormula
AT2:AT51AT2=IF(AND($AL2="Sunday",$AK3&$AL3<>$AK2&$AL2),COUNTIFS(F$2:F2,"Global",AK$2:AK2,AK2),"")
AU2:AU51AU2=IF(AT2="","",COUNTIF(AK$2:AK2,AK2)-AT2)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Conditional formatting based on value of another cell
Replies
3
Views
256
etaf
etaf
B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
190
bh24524
B
T
  • Question
Conditional Formatting for Date Comparisons
Replies
5
Views
204
etaf
etaf
P
  • Question
Applying conditional formatting to merged cells
Replies
1
Views
319
Joe4
Joe4
C
  • Solved
Formula Help (Calendar) Expanded
2
Replies
15
Views
724
Eric W
Eric W

Forum statistics

Threads
1,215,135
Messages
6,123,238
Members
449,093
Latest member
Vincent Khandagale

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