Question: In Excel 2007, I have a table with numbers in Columns F, G, H, I, and J. There is an identifier in Column B. 

I need the average of all numbers in Columns F, G, H, I, and J where the identifier in Column B = A14 (i.e. the value in A14). 

I tried the following:

```
=AVERAGEIFS('Hours-Backup'!F3:J1048576,'Hours-Backup'!B:B,'Group Dashboard'!A14)

```

This results in a **`#VALUE!`** error.

Ultimately, I want to make it so that the function ignores all cells that are non-numeric or blank. In other words, I don't want it to assume that blank cells are 0 for averaging purposes. 

I can't figure out what the problem is.

Answer: 
Two problems.  (Or one problem with two heads.)  `AVERAGEIFS` help says:

> 
> * Unlike the range and criteria arguments in the AVERAGEIF function,
>  in AVERAGEIFS each criteria\_range must be the same size and shape as sum\_range.
> 
> 
> 

Well, that’s a typo: “sum\_range” should be “average\_range”. 
But, anyway, that’s your problem: your criteria range is 1×∞ (an entire column)
and your average range is 5×1048574. 
So you can fix one of the problems by changing `B:B` to `B3:B1048576`,
making it a 1×1048574 range.

You could solve the second problem
by replicating Column `B` into Columns `C`, `D`, `E`, and `F`,
and then using `B3:F1048576` as your criteria\_range.  But that’s crazy. 
The reasonable way is to define a helper column (let’s say `V`)
to be the average of Columns `F`-`J` on the current row:

* Set `V3` to `AVERAGE(F3:J3)`,
* and drag down.

Then get your conditional average by averaging the averages:

```
=AVERAGEIFS('Hours-Backup'!V3:V1048576,'Hours-Backup'!B3:B1048576,'Group Dashboard'!A14)
(changes)                  ^  ^                        ^  ^^^^^^^

```
My approach to situations like this is a couple of helper columns and some array formulas. Hopefully I've set this toy sheet up in the same way as yours:

![Excel snip](https://i.stack.imgur.com/RbrF4.png)

Here I've set up `A16` as the cell to set which filtered rows you want to retain. `B` is the column of values to filter against, `C` through `G` are the columns with values that may be missing. I count the number of values present in each row in column `H`, and sum the values that are present in column `I`. Cells `H2` and `I2` sum the respective 'Count' and 'Sum' columns, and then the resulting average is computed in `K2`. Note that the formulas in columns `H` and `I` are nicely robust to both text and error values within the table.

I'm not sure of the version compatibility of the `SUM(IF(...))` construction within array formulas. It clearly works on Excel 2010 and should probably also work on Excel 2007, and on Excel 2013.