# How to write AverageIf (Not an Empty String) in Google Sheets?

I have the answer to this question. However, it took me a few wasted hours of research and work to figure it out. I couldn't find an answer anywhere online, so I thought I'd write out a question and share the answer in the hope of saving someone (who is as dumb as me) some time.

I've been working on a Google Spreadsheet for a while to get attendance counts for a few classes. I have a Google Form where teachers input the attendance for their class. That automatically gets sent to a Form Response spreadsheet. From there I have a few sheets that manipulate the input data, then a summary sheet that shows a table of: classes (rows), dates (columns), and attendance to each class on specific dates. Each cell in the table contains a formula which either returns the attendance count or an empty string. Until data exists for a date and class, that cell contains and empty string. The table works perfectly.

Using the data in this table, I want to get average attendance for each class in real time. There are a couple of problems though:

1. The table is set out for the whole year, so there are a lot of cells containing empty strings until the end of the year, which mess up the "real time" average
2. Occasionally a class's attendance doesn't get filled in, so there are random cells with empty strings which mess up the average throughout the year

I've been trying AVERAGE, IF, and AVERAGEIF functions to figure this out. AVERAGEIF seems to be the solution but I can't figure out a criteria that works. "<>""" causes a return value of 0 no matter what range I'm trying to average. I've thought that using ISNUMBER(parameter) as the criteria, but I don't know what to use as a parameter for it. Not surprisingly, using it without a parameter didn't get me anywhere (DIV/0 error)

I haven't found Google's Docs editor Help to be super helpful, so I was wondering if anyone might be able to share how to use AVERAGEIF(does not contain an empty string).

• Did you try just "<>" or "<>" & ""
– Scott Craner
Feb 15, 2017 at 20:31
• "I have the answer to this question" - that's nice but it's off topic. Please read the google-spreadsheets tag ... Feb 15, 2017 at 20:50
• If you set the datatype for the column, wouldn't this problem disappear?
– Yorik
Feb 15, 2017 at 20:54
• Thanks for the guidance @David. By the time I was adding tags I was having some computer speed issues and wasn't really paying attention. I saw a few other google spreadsheet questions on the site, so assumed it was fine. Now I know! Mar 1, 2017 at 5:45

## 4 Answers

=AVERAGEIF(A1:Z1,"<>")

"<>" on its own means "not equal to an empty string" in Google Sheets.

• @pnuts Yes, I did. It kept including all the blank cells in the calculation as if they were zeroes. Mar 1, 2017 at 5:47
• That's weird, I've just tested the formula on a range that includes blank cells and they were not counted. Maybe you could post a link to the spreadsheet where it occurs so that the problem could be debugged? Mar 14, 2017 at 16:15
• It's weird it deosn't work for me.. =AVERAGEIF(F2:N2,"<>") all the cases between F2 and N2 are cells with data validation, based on constantes of another page. Only one is filled, so it should give 1/9 percent Jul 14, 2018 at 12:46

I recommend using:

=IF(COUNT(E4:G4);AVERAGE(E4:G4);0)

COUNT() returns numbers of cells containing valid numbers : if none, the value will be 0, so the condition will evaluate to false and return 0 (you can put "" instead of 0 if you prefer). Otherwise, it will use the result of the AVERAGE function.

• Only thing that really worked for me--and so obvious in retrospect! Aug 22, 2020 at 16:16

To all future viewers, easiest way is Sum(A1:Z1)/Counta(A2:Z1)

• Hey! Welcome to Web Applications! You should expand on your answer for it to be really helpful. What does the cells in you example relate to? Feb 28, 2019 at 8:19
• This is the only answer that I could get to work. The downvote by above commenter is really uncalled for. The commenter is asking for context which the question and other answers clearly show. Thanks Sydney! Jan 13, 2020 at 0:49
• One downside to this is that the range has to be specified twice Dec 7, 2020 at 19:59

I also encountered (DIV/0 error) when attempting to use AVERAGEIF to check for empty string. In my case, I only want to take an average if a text column is non-empty, as the number columns will always be populated in my sheet but are only valid if the text column is populated.

My solution is to use an IF function:

=IF(A2 <> "", AVERAGE(D2:AF2), "")

This is what I'd expect AVERAGEIF to do on it's own, but it doesn't.

• Excellent solution! It works! May 2 at 1:16