When 2 columns meet criteria find the max in another column and subtract from another col

WANT F5 TO = THE RESULTS WHEN (SHEET 2012_V COL "F"=165 & COL "N"=1 FIND THE MAX NUMBER IN COL "D")
THEN SUBTRACT "AF5" ON THIS SHEET) ANSWER SHOULD BE 13

Please refer to the attached example.

Many thanks

Hello
Try the following formula in cell F5:

=IF(AND('2012_V'!L11=165,'2012_V'!N11=1),MAX('2012_V'!$D$11:$D$27)-AF5,"")

I had to convert your numbers from text to numbers in columns L and N on the 2012_V sheet to get this to work. Is there any reason why they were formatted as text?

Hope this helps

Formula had no error messages but it did not produce an answer which should be 13. The reason columns L and N were formatted as TEXT is because these columns may contain contain text and alphanumeric data. could the problem be solved by putting "quotation marks" around the data? I did convert columns L & N to numbers before trying the formula. Also the L11 should be L:L, N11 should be N:N and the $d$11:$d$27 should be D:D

Thanks

Hi
I'm not sure I can help on this without more information. What text is contained in column N and L for example and what exactly are you counting in those columns?

DBY

I have uploaded example4 to provide more information which shows several examples of data in columns N:N and L:L. Not counting anything in columns N:N and L:L. Once the criteria for columns N:N and L:L are met the formula needs to check column D:D for the MAX NUMBER then subtract the MAX NUMBER from the number in AG5. The result is the number of weeks the we have been receiving revenue which is then divided by M5 to get the average volume.

Hello again
The only way I can think to do this is with an Array formula:

=MAX(IF(('2012_V'!L:L=H5)*('2012_V'!N:N=J5),'2012_V'!D:D,""))-AG5

In cell F5 entered with CTRL+SHIFT+ENTER and copied down.

It's perhaps not a good idea in Excel 2007 to reference an entire column like this as there are 1048576 rows and the formula will be slow. A set smaller range or a dynamic named range might be a better option.

Hope this helps
Sorry for the earlier confusion.
DBY

Formula returned a 0 for the answer. Could it be that the '2012_V'!D:D,"" part of the formula is not allowing the selection of the Maximum number in the D:D column?

It may be easier to add a column to the totals sheet such as AG6 that would have a formula that computed the following

Go to 2012_V
if L:L= 165 & N:N = 1
go to D:D and find the MAX number and post in AG6

If you agree, I would appreciate your suggesting the formula.

I will narrow the column search range down as you suggested.

Many thanks,
mrp

Hi
It returns 0 because there is no match of 165 and 1 in your data on the 2012_V sheet. If you filter the C column you will see this is so insert a 1 and it calculates very slow!

DBY

I outsmarted myself by changing the 1 to main to show text in the field.

Problem solved and many thanks to you.......

mrp