Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-09-2021, 04:53 AM
CuriousG CuriousG is offline Tricky conditional formatting and sumif function Windows 10 Tricky conditional formatting and sumif function Office 2016
Novice
Tricky conditional formatting and sumif function
 
Join Date: Jan 2021
Posts: 20
CuriousG is on a distinguished road
Default Tricky conditional formatting and sumif function

I have a table of invoices. If a new invoice is added and that customer already has X invoices within the last 90 days that are not recorded as "Paid" in the status column then I want the new invoice entry to be formatted with a red background.

I also want to maintain a running total of the value of all invoices whose status is "Paid", "Pending" etc.

I want to achieve this by using formulas and not vba if at all possible.



The attached spreadsheet should fill in any gaps in my explanation.
Attached Files
File Type: xlsx Testing formulas.xlsx (12.5 KB, 5 views)
Reply With Quote
  #2  
Old 12-09-2021, 06:35 AM
ArviLaanemets ArviLaanemets is offline Tricky conditional formatting and sumif function Windows 8 Tricky conditional formatting and sumif function Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Here you go!

Edit
About totals: When you set any filter to data table, the totals are calculated for visible rows only (i.e. they work like subtotal)!
Attached Files
File Type: xlsx Testing formulas.xlsx (20.4 KB, 6 views)
Reply With Quote
  #3  
Old 12-09-2021, 07:00 AM
CuriousG CuriousG is offline Tricky conditional formatting and sumif function Windows 10 Tricky conditional formatting and sumif function Office 2016
Novice
Tricky conditional formatting and sumif function
 
Join Date: Jan 2021
Posts: 20
CuriousG is on a distinguished road
Default Thanks for your help

WOW that was quick I'm very grateful
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel function sumif help ganesang Excel 8 09-05-2021 08:53 PM
Tricky conditional formatting and sumif function Conditional formatting technofrank Excel 3 06-08-2015 03:15 AM
Conditional Formatting sdfrance Excel 3 01-06-2015 07:10 AM
Tricky conditional formatting and sumif function WEEKDAY Function with Conditional Formatting meggenm Excel 1 08-09-2014 09:42 AM
Tricky conditional formatting and sumif function Conditional formatting that ignores other formatting rules info_guy2 Excel 1 07-03-2014 10:07 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:23 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft