Return a value based on status of multiple cells

0

Hello-

I have been trying to use INDEX/MATCH and other combinations to get the desired result but have been unsuccessful.  I am dealing with a one to many relationship and trying to return a value, but if one of the values associated to the Index is blank, then return no value.

I am working with multiple permits that are associated to one location and only if all required permits are submitted should it return the most current date.  If one of the pemits is not submitted, then no date should be returned.  

I attached an example of what I am trying to do.  Thank you in advance for any help you can provide.

Answer
Discuss

Answers

0
Selected Answer

Because it's easier to read formulas with named ranges and because it's easier to specify dynamic ranges when they are named I created two named ranges in your worksheet as follows.

  1. B5:B8 As "Sites"
  2. D5:D8 As "Submitted"

In the formulas below you may replace the names with the hard range addresses if you prefer.

Now consider this IF statement. It says, if the number of occurrences of "Orange" in Sites is equal to the number of dates associated with "Orange" in Submitted, then the condition is True, otherwise it's False.

=IF(COUNTIF(Sites,K5)=COUNTIFS(Sites,K5,Submitted,"<>"),TRUE,FALSE)

This solves a part of your problem. If a date is missing you want to return "". Otherwise you want the most recent of the dates. This is the formula that extracts the latest of the dates associated with "Orange".

=MAX(IF(Sites=K5,Submitted))

This formula will return what I call a "True date", meaning, it will return a number. To display the number as a date you need to format the cell. The date will then be displayed in the format you have set for the cell.

Now all that remains to be done is to replace the two results in the IF statement first above introduced. Paste the merged formula to L5 and copy down as required by column K. (The formula will throw an error if the named ranges haven't been set up first.)

=IF(COUNTIF(Sites,K5)=COUNTIFS(Sites,K5,Submitted,"<>"),MAX(IF(Sites=K5,Submitted)),"")

In your Excel 365 this formula will work just as it is but if someone has an older Excel version it must be entered as an array formula with CTL + SHIFT + ENTER.

Discuss

Discussion

I cannot thank you enough for not just solving my issue but the detailed explaination of the logic and steps.  I am very grateful for your help.  Thank you.

R/s,
Joe
JSacca (rep: 2) Apr 1, '21 at 3:03 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login