Solved

I need an excel formula

Posted on 2014-03-04
19
259 Views
Last Modified: 2014-03-05
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
Comment
Question by:wrt1mea
  • 10
  • 9
19 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 39904413
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
 
LVL 1

Author Comment

by:wrt1mea
ID: 39904484
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
 
LVL 23

Expert Comment

by:NBVC
ID: 39904497
So even if 3 out of 4 instances of a match contain a date, then you want a blank?
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 39904507
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
 
LVL 1

Author Comment

by:wrt1mea
ID: 39904509
and 4 was an example. could be 1, 2, 5, 18.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39904511
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
 
LVL 1

Author Comment

by:wrt1mea
ID: 39904546
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
 
LVL 23

Expert Comment

by:NBVC
ID: 39904575
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
 
LVL 1

Author Comment

by:wrt1mea
ID: 39904584
I didnt think about that....sorry! the column q has a formula in it looking at other cells
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 23

Expert Comment

by:NBVC
ID: 39904629
Can you show that formula here?
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 39904640
Its on the example I provided. column q:

=IF(P2="",IF(J2="",IF(O2="","",O2),J2),P2)
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39904660
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
 
LVL 1

Author Comment

by:wrt1mea
ID: 39904699
Still returning a date for ones that dont have all of the date info completed.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39904731
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
 
LVL 1

Author Comment

by:wrt1mea
ID: 39904744
See the attached....
EXECH03-4-14.xlsx
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39904764
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
 
LVL 1

Author Comment

by:wrt1mea
ID: 39904778
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
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39905083
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
 
LVL 1

Author Closing Comment

by:wrt1mea
ID: 39907185
BOOM!!! YOU ARE THE MAN!

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now