Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

I need an excel formula

Posted on 2014-03-04
19
Medium Priority
?
276 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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 how to use a scrolling table in Microsoft Excel using the INDEX function.

597 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