Forum Discussion

ddigger62's avatar
ddigger62
Copper Contributor
May 24, 2018

Excel Formula Help

Hello,

 

I need assistance with an Excel formula, please.  I've been struggling with this for hours; your help is greatly appreciated.

 

I have a spreadsheet with 3 columns.  The three columns are entitled "Month", "Year" and "Amount."  There are about 10,000 rows that go forward over 5 years.  I need to enter a formula that searches the entire number of rows (10,000 rows) and looks for all rows where the Month = (i.e.) May, and the Year = (i.e.) 2020, then display the minimum number in the "Amount" column of the entire range that meets the aforementioned criteria (column A cell = "May" and column B cell on the same row = "2020").  Hope this makes sense.  I've looked at various formulas and am having trouble making excel understand I want it to return the minimum value in cell C of just the rows that show column A and B are May 2020.  I've tried IF, MIN, INDEX, MATCH, etc., with no success.  Thank you.

Formulas & Functions
Need Help formula
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    May 24, 2018

    Hi

     

    the easy way would be a pivot table. "Year" and "Month" in row area and "Amount" in values area. Change the aggregation function to "Min".

    Or use MINIFS():

    =MINIFS(C2:C10150,A2:A10150,"May",B2:B10150,2020)
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    May 24, 2018

    Hi,

     

    Please try this formula:

    =MAX(IF((E4=A2:A20)*(F4=B2:B20),C2:C20))

     And find it in the attached file.

     

    Note: To enter this formula, you have to press Ctrl+Shift+Enter at the same time, because it's an array formula.

     

    Regards

     

Resources