Despite labouring over Contextures Index/match for multiple criteria blog, I remain unable to find a solution.
My data set looks like this
Column A: Dates
Column B: Country
Column H: Value
I want to find the earliest date for a particular country which contains a corresponding value above zero in column H.
Help very much appreciated.
Hard to be more precise than this without seeing any sample data, but try using this ARRAY formula...
={MAX(IF(B2:B7="Country name",A2:A7,0))}
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thanks FDibbins but I didn't output the date I was looking for.
The formula needs to output the earliest date for a particular country which has a value in column H.
It's probably easier if attach an excerpt of the data set with desired formula output indicated. Hope that helps.
Earliest date for country.xlsx
Thanks for the file, makes thinks a lot easier
Try this ARRAY formula in E12, copied down...
=MAX(IF($B$2:$B$7=D12,IF($H$2:$H$7>0,$A$2:$A$7,0)))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
shouldnt that be min ?
= MIN( IF( ($B$2:$B$10=D12)*($H$2:$H$10>0), $A$2:$A$10 ) ) again array entered
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Not quite workingDoesn't the max function seek the latest date, rather than the earliest? I tried changing to MIN and that gave the correct output except Austrailia which should be the 8-Jun....
did you not read post #5
Martin - that one works!
Thank you both for your help. Consider it solved
oops sorry, thanks for the catch Martin
Happy to help and thanks for the feedback
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks