• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

I need an excel formula

I am needing an excel formula to search with specific variables and return the most recent date out of a list.

I am using the following formula that has almost all of the variables I need, except for the part that with search through the list of dates, and return the most recent.

=IF($Q10="","",SUMPRODUCT(('R:Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000=$Q10)*('R:\Reports\Count\[Count.xlsx]MFDATA'!$M$2:$M$5000="N")*('R:\Reports\Count\[Count.xlsx]MFDATA'!$C$2:$C$5000<>"")*ISNA(MATCH('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000,'R:\Reports\Count\[Count.xlsx]MFINFO'!$D$5:$D$7,0))))

The column with the date in it would be: ('R:\Reports\Count\[Count.xlsx]MFDATA'!$Q$2:$Q$5000

And I know a sumproduct is not what I am needing, I was trying to using an index/match with the MAX function but was having problems with all of the variables.,
0
wrt1mea
Asked:
wrt1mea
  • 10
  • 9
1 Solution
 
NBVCCommented:
Try:

=IF($Q10="","",MAX(IF(('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000=$Q10)*('R:\Reports\Count\[Count.xlsx]MFDATA'!$M$2:$M$5000="N")*('R:\Reports\Count\[Count.xlsx]MFDATA'!$C$2:$C$5000<>"")*ISNA(MATCH('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000,'R:\Reports\Count\[Count.xlsx]MFINFO'!$D$5:$D$7,0)),'R:\Reports\Count\[Count.xlsx]MFDATA'!$Q$2:$Q$5000
)))

confirmed with CTRL+SHIFT+ENTER not just ENTER
0
 
wrt1meaAuthor Commented:
Yes! This works, but you also exposed a flaw.

I need to make sure that it returns the max value ONLY IF Column Q has values in it. For example, if there were 4 diff dates in column Q, it returns the most recent! GREAT!!

If there are only 1 date out of 4 instances of it matching that in column q:

'R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000=$Q10)

Then, I need it to return blank, until all of the instances have a value...and they have met the other criteria.


Should I open a diff question?
0
 
NBVCCommented:
So even if 3 out of 4 instances of a match contain a date, then you want a blank?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
wrt1meaAuthor Commented:
yes sir....I am returning it on a prod sheet and dont want to show the last date until the project has dates in every field, thus signifying the project is closed.
0
 
wrt1meaAuthor Commented:
and 4 was an example. could be 1, 2, 5, 18.
0
 
NBVCCommented:
If so, try:

=IF(Q10="","",IF(SUMPRODUCT(('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000=$Q10)*('R:\Reports\Count\[Count.xlsx]MFDATA'!$M$2:$M$5000="N")*('R:\Reports\Count\[Count.xlsx]MFDATA'!$C$2:$C$5000<>"")*ISNA(MATCH('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000,'R:\Reports\Count\[Count.xlsx]MFINFO'!$D$5:$D$7,0))*('R:\Reports\Count\[Count.xlsx]MFDATA'!$Q$2:$Q$5000=""))>0,"",MAX(IF(('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000=$Q10)*('R:\Reports\Count\[Count.xlsx]MFDATA'!$M$2:$M$5000="N")*('R:\Reports\Count\[Count.xlsx]MFDATA'!$C$2:$C$5000<>"")*ISNA(MATCH('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000,'R:\Reports\Count\[Count.xlsx]MFINFO'!$D$5:$D$7,0)),'R:\Reports\Count\[Count.xlsx]MFDATA'!$Q$2:$Q$5000))))

Open in new window


confirmed with CTRL+SHIFT+ENTER not just ENTER
0
 
wrt1meaAuthor Commented:
It' still returning a date for ones that dont have all of the dates in them.

Trying to get something for you to see. Have to obfuscate some data.
0
 
NBVCCommented:
Are the blanks real blanks? Or maybe there is a space it them, or a 0 formatted as blank?

Can you check that too?
0
 
wrt1meaAuthor Commented:
I didnt think about that....sorry! the column q has a formula in it looking at other cells
0
 
NBVCCommented:
Can you show that formula here?
0
 
wrt1meaAuthor Commented:
Its on the example I provided. column q:

=IF(P2="",IF(J2="",IF(O2="","",O2),J2),P2)
0
 
NBVCCommented:
Hmmm.  Looks ok, as long as O, J and P are returning spaces, etc.

Give this version a quick test...

=IF(Q10="","",IF(SUMPRODUCT(('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000=$Q10)*('R:\Reports\Count\[Count.xlsx]MFDATA'!$M$2:$M$5000="N")*('R:\Reports\Count\[Count.xlsx]MFDATA'!$C$2:$C$5000<>"")*ISNA(MATCH('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000,'R:\Reports\Count\[Count.xlsx]MFINFO'!$D$5:$D$7,0))*(ISTEXT('R:\Reports\Count\[Count.xlsx]MFDATA'!$Q$2:$Q$5000)+('R:\Reports\Count\[Count.xlsx]MFDATA'!$Q$2:$Q$5000="")))>0,"",MAX(IF(('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000=$Q10)*('R:\Reports\Count\[Count.xlsx]MFDATA'!$M$2:$M$5000="N")*('R:\Reports\Count\[Count.xlsx]MFDATA'!$C$2:$C$5000<>"")*ISNA(MATCH('R:\Reports\Count\[Count.xlsx]MFDATA'!$B$2:$B$5000,'R:\Reports\Count\[Count.xlsx]MFINFO'!$D$5:$D$7,0)),'R:\Reports\Count\[Count.xlsx]MFDATA'!$Q$2:$Q$5000))))

Open in new window


If it still is not working, then if you can post a sample workbook with those formulas in place that would be easiest to see what may be the problem.
0
 
wrt1meaAuthor Commented:
Still returning a date for ones that dont have all of the date info completed.
0
 
NBVCCommented:
I would need to see a sample then...  if the original formula worked, then I am not sure what else would not allow this one to work.
0
 
wrt1meaAuthor Commented:
See the attached....
EXECH03-4-14.xlsx
0
 
NBVCCommented:
I am on my way out... I will look at it later tonight, if that is okay?

I noticed that your formula references B2:B5000 twice.  Is that supposed to be?
0
 
wrt1meaAuthor Commented:
Not a problem at all! I appreciate it. I am getting ready to leave the office for the day.

Yes, I need to reference B2:B550 twice. Once to find matching number codes, and once to exclude a set of number codes from a specific list.

Thanks for all of the help and tenacity.
0
 
NBVCCommented:
Hi,

Okay, I tried my original formula and it works fine on your sample workbook.

I only removed the filenames but left the sheetnames and references in tact.

I moved the formulas to MFINFO sheet, in A1 copied to A2.  

Formula in A1 references Q10 and A2 references Q11.  I changed the column B values in lower table of MFDATA sheet to 200 so that I didn't have to change the formula to accommodate separate tables.

You will see that I got the expected results.

Please review and see what might be different in your original file.

P.S.

I am confused about your statement

Yes, I need to reference B2:B550 twice. Once to find matching number codes, and once to exclude a set of number codes from a specific list.

If you are looking for a specific criteria (like 100), then that will automatically exclude anything that may be in D2:D7.  I mean the two checks are mutually exclusive....

If that is the case, then you don't need the ISNA(MATCH()) part.
Copy-of-EXECH03-4-14.xlsx
0
 
wrt1meaAuthor Commented:
BOOM!!! YOU ARE THE MAN!

Thank you very much for your help and persistence. Works perfectly!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now