Populate the higher of two values based on date.

beadgirlsa

New Member
Joined
Feb 16, 2011
Messages
14
I would am trying to make a total of 2011 sales in Column A.

A(Invoice2011) B(Renewal) C(New Net) D (Desired)
1/15/2011 0 $500 $500
2/23/2011 $420 $0 $420
5/20/2011 $100 $0 $0

If the Invoice2011 date is >=TODAY() in the invoice column, I want Column D to show the higher of the two columns (or, the column that contains data, because only B -or- C should contain data, but not both)

This way, I can get an up-to-date total of the sales in 2011.

I've written several formulas (which I could paste here but none of them has been right) so asking for assistance from someone who may be able to help.

Thank you in advance for any expertise you may be able to offer.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

AlisaA

Board Regular
Joined
Mar 17, 2010
Messages
193
I'm a little confused as to what you are trying to do here. You state that you are looking to to get an up-to-date total of 2011 sales, but you want column D to only show data when the invoice date is greater than or equal to TODAY(). I'm not sure I understand how that is going to give you 2011 to date, as it is going to exclude more 2011 data every day...???
 

Haseeb Avarakkan

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
Hello,

As per your original post, the desired result will give you only if Invoice Date <=TODAY().

Enter in a non usable cell, eg: H1 = TODAY(), then D2 copy down...

=MAX(B2:C2)*(A2<=$H$1)
 

Forum statistics

Threads
1,141,740
Messages
5,708,235
Members
421,553
Latest member
Geeyj

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
Top