Hi, I'm trying to make get the max date out of column D for any row where column A = a text string in another tab from a pivot table, column B = "cont mod", and column C = "1".
The formula needs to be draggable, and in a seperate tab than columns A through D.
I've tried formulas with arrays, index, Max(if(and()))), etc, and have not had any luck yet.
My best guess was:
=IF (AND ( a2:a1200 = "text string from other tab's pivot table" , b2:b1200 = "cont mod", c2:c1200 = 1), MAX (AE2:AE1200),)
and then I make it an array with CSE. However, it is giving me 1/1/1900, which is defintely not correct.
Any help is greatly appreciated.
Last edited by Greg Kinney; 11-23-2011 at 11:11 AM.
I should have also mentioned that some of the data in these cells are blank (especially the date column that I'm trying to get returned). I'm also going to need something very similar for the min date out of column E.
I tried to do a =max(vlookup array formula also, but that didn't work either (I was really reacching on that one).
Hello Greg, try this
=MAX(IF(A2:A1200="text string from other tab's pivot table",IF(B2:B1200="cont mod",IF(C2:C1200=1,AE2:AE1200))))
For MIN you will probably have to exclude blanks too, so that would be
=MIN(IF(A2:A1200="text string from other tab's pivot table",IF(B2:B1200="cont mod",IF(C2:C1200=1,IF(AE2:AE1200<>"",AE2:AE1200)))))
both formulas are "array formulas" which means they need to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula
Audere est facere
This worked great, thank you so much. Looks like I was jsut trying to over-engineer an answer with my previous attempts. I don't like nesting too many "ifs", but that clearly wound up being simper than all the other stuff I was trying.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks