?
Solved

I need an excel formula

Posted on 2014-03-04
19
Medium Priority
?
270 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

801 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