Hi All,
I have come undone in trying to solve this one, I need to return the maxium value of DDH1234, DDH1235 (etc..) from column C, provided that column D = "NO". See the attached image, may make it a little clearer.
Thanks in advance.
Chan
Excel.png
Last edited by chan069; 11-21-2012 at 06:28 AM.
MAX(IF(A1:A100="DDH1235";IF(D1:D100="NO";CI:C1000)))
Array formula.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Formula:
=SUMPRODUCT(--MAX(--(A1:A500=DDH1234)*--(D1:D500="No")*--C1:C500))
Adapt the ranges if required.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Hi Sixthsence,
I adapted your formula, however with no success as it returned a value of "0" whereas it needs to return "64", as shown in the attached image in the original post. Any help would be appreciated.
Thanks,
Chan
Try this..Originally Posted by chan069
Formula:=SUMPRODUCT(--MAX(--(A1:A500=DDH1234)*--(D1:D500="No"),C1:C500))
Hi Sixthsence,
Seems to return the maximum value of column C, and therefore not accounting for whether it is yes or no. Thoughts?
Regards,
Chan
Hm.. Please ignore my post 11 it's just junk.
I just missed to quote the DDH1234 inside the quotes like this "DDH1234".
Formula:=SUMPRODUCT(MAX(--(A1:A500="DDH1234")*--(D1:D500="No")*--(C1:C500)))
Hi Fotis,
Thank you for the post, however when testing the above formula it doesn't seem to work? It does not recognize DDH1235, despite the quotations (""). To rectify this I attempted just linking directly to a cell which had DDH1235, however this still failed. Amy thoughts?
Thanks,
Chan
Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
Never use Merged Cells in Excel
Thanks for the friendly reminder.
Regards,
Chan
Hello,
create a pivot table with the codes in the row labels, the value of column B in the Value pane, set "max" as the value field settings and filter the data by the desired value in column D.
regards, LMP
Multiply ideas, if.
1) Do what Moderator asked for you.
2) Upload a sample workbook and not a picture. Can you work on a picture?
No problems, see above.
Regards,
Chan
=MAX(IF(A2:A1000="DDH1235",IF(D2:D1000="NO",C2:C1000)))
Array formula. cONFIRM USING cONTROL+sHIFT+eNTER-nOT JUST ENTER.
See this workbook.
It generates a unique list of your codes, and the max if yes or no.
Grag H2 Across then Down.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
Thank you to you all for your help. Both the above formula and pivot tables worked a treat.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks