Is it possible to add a criteria of blank cells in Averageifs?

[sbea114]

Hello,
I am using the formula AVERAGEIFS to find an average of numerous column of numbers, but I want to have two different average results; 1) Criteria:24,Normal, 75Hz,1. 2) Criteria: 24,Normal,75Hz, Here I want to add a criteria of blanks). In my data some cells have "1" as the value and some are blanks as, hence I want to find the results to both kinds. The formula I used for the former is =AVERAGEIFS(L24:L234,A24:A234,24,B24:B234,"normal",G24:G234,"75hz",I24:I234,1) and it seemed to work, but the problem is the latter condition which I want to include blank cells as a criteria.

Thank you for reading through.

[gak67]

If you want the average of just the blank cells try this (as long as there are only 1's and blanks):

Please Login or Register  to view this content.

If you want the average of all the cells, including blanks, try this:

Please Login or Register  to view this content.

[sbea114]

Thank you firstly,
I tried the first formula because the column does only contain 1's or blanks but it gave me the result #DIV/0! error.

[gak67]

It works on some dummy data I created here. Can you upload a copy of your sheet (with any confidential data removed)? (Go Advanced, then Manage Attachments).

[sbea114]

Here is the dummy file you requested, hopefully it is testable. I've also added the labels for the categories which needs to be included in the average formula.
Thank you.

[gak67]

So the first Formula should be this:

Please Login or Register  to view this content.

The second formula should be this:

Please Login or Register  to view this content.

Without the IFERROR part it also showed a #DIV/0 here, but that was because the dummy data had no 24" screens with 75Hz and no HDMI inputs. I changed the data to check that it worked, and it does.

[sbea114]

Thank you so much!
I tried it without the IFERROR part and it worked and gave me the results I wanted.
Your very is much appreciated.