Excel 2016 formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-2105769%22%20slang%3D%22en-US%22%3EExcel%202016%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2105769%22%20slang%3D%22en-US%22%3EI%20have%20three%20following%20formula%20%3DSUMIFS(C21%3AHI21%2CC3%3AHI3%2C%22Mon%22)%2FCOUNTIFS(C3%3AHI3%2C%22Mon%22).%20Some%20of%20my%20%22Mon%22%20columns%20are%20blank%20and%20I%20need%20the%20formula%20to%20ignore%20them.%20I%20have%20tried%20several%20options%20and%20am%20not%20having%20any%20luck.%20Can%20someone%20help%20please%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2105769%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2105905%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202016%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2105905%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F945986%22%20target%3D%22_blank%22%3E%40Zaidee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20AVERAGEIF%20and%20AVERAGEIFS%20functions%20ignore%20blank%20cells.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DAVERAGEIFS(C21%3AHI21%2CC3%3AHI3%2C%22Mon%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor
I have three following formula =SUMIFS(C21:HI21,C3:HI3,"Mon")/COUNTIFS(C3:HI3,"Mon"). Some of my "Mon" columns are blank and I need the formula to ignore them. I have tried several options and am not having any luck. Can someone help please?
1 Reply

@Zaidee 

The AVERAGEIF and AVERAGEIFS functions ignore blank cells.

 

=AVERAGEIFS(C21:HI21,C3:HI3,"Mon")