Forum Discussion

BlueMoose's avatar
BlueMoose
Brass Contributor
Jun 27, 2023
Solved

AVERAGEIF(S) Based on Another Cell

I am looking to find the average of column AM; but, only when column B matches what is in AH1.
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 27, 2023

    BlueMoose 

    As variant  expand to 

    =AVERAGE( IF(col_1=col_2, IF( ISERROR(val), "", val ) ) )

     

Patrick2788's avatar
Patrick2788
Silver Contributor
Jun 27, 2023

BlueMoose 

Try this one:

=AVERAGE(IF(Col_1=Col_2,val))

 

Sheet: 

 

BlueMoose's avatar
BlueMoose
Brass Contributor
Jun 27, 2023
That still gives me the same issues of when it's averaging a section of the cells that has #VALUE due to the data not being completely updated. I need a way for it to disregard the #VALUE and average everything else.
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 27, 2023

    BlueMoose 

    As variant  expand to 

    =AVERAGE( IF(col_1=col_2, IF( ISERROR(val), "", val ) ) )

     

    • BlueMoose's avatar
      BlueMoose
      Brass Contributor
      Jun 27, 2023
      Perfect! That did it exactly. Thanks so much, Sergei!

Resources