Max If function not using criteria

[Jietoh]

Hello all!

I'm trying to write a formula that will find the max value in a column based on two criteria, and month and the Fiscal Year. I have the full date typed out in column A, and then formulas in columns B and R to pull the Month and Fiscal Year respectively.

The data I want to find the Max of is in column P. What I have so far is....

Please Login or Register  to view this content.

Essentially, here I want it to find the max value for July of 2013. Yet, this returns a negative value. I have toyed around with many different variations of this, even forgoing referencing columns B and R altogether by using the following...

Please Login or Register  to view this content.

, but this only returns the max in my entire range, not the condensed criteria driven version.

I've attached a spreadsheet with a snapshot of what I am working with, but my full list has over 5000 entries. Test2.xlsx

Thanks in advance.

[AlKey]

Try this

=MAX(IF((B:B=7)*(R:R=2013),P:P))

I would also recommend to set ranges for better performance.

..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.

[mcmahobt]

In which cell do you plan on implementing this formula?

[Jietoh]

AlKey - When I tried your solution in the Test doc that I uploaded I got a value of 0, and when I tried it in my intended file it gave me a REF error.

mcmahobt - I actually intend this cell to be on a different sheet

[AlKey]

AlKey - When I tried your solution in the Test doc that I uploaded I got a value of 0, and when I tried it in my intended file it gave me a REF error.

mcmahobt - I actually intend this cell to be on a different sheet

Did you use CTRL+SHIFT+ENTER to enter formula?. Also, make sure that your criteria in B:B and R:R are actually numbers and not text.
If you still have problems please upload a sample spreadsheet.

[Jietoh]

AlKey - I did CSE the formula. However, I continued tweaking it, and finally go it to work this morning.

However, now I'm having another problem. The original goal (which I did not post about) was to write a formula that would display the account # of the highest single cost during a given period (in my example July 2013). After I got the above code to work, I began on the second part. I finally got the following to work on the same sheet as the data. (This formula is entered as a CSE).

Please Login or Register  to view this content.

However, when I take the same formula and move it to another worksheet and adjust the cell references I get a value of 0.

Please Login or Register  to view this content.

Again, this code is entered as a CSE value.

Any ideas why its not working. The only thing that changed was moving it to a new sheet, and the updated cell references should have accounted for that.

[Jietoh]

I found the issue.

Please Login or Register  to view this content.

I forgot to reference this piece back to the other worksheet.

Thanks for your help.