AVERAGEIF function for multiple columns.

Try this array formula**:

=AVERAGE(IF(A3:A26="",D3:H26))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Have you triede/g:

=AVERAGEIF(A3:C26, "", D3:H26)

=SUMPRODUCT((A3:A26<>"")*(D3:H26))/(5*COUNTA(a3:26))

Not sure how you got that result!

Here's a small example:

Data Range

	A	B	C	D	E	F	G	H
1	44.2	----	----	----	----	----	----	----
2
3	42			84	52	56	54	32
4				39	49	17	60	56
5	62			63	68	17	73	11
6	2			56	30	44	53	72
7	39			67	71	28	8	92
8	8			20	97	31	76	2
9	70			14	31	2	42	69
10	21			40	64	57	3	69

This array formula** entered in A1:

=AVERAGE(IF(A3:A10="",D3:H10))

Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you must use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you must use the key combo to produce them. Also, anytime you edit an array formula it must be re-entered as an array using the key combo.

ops i made a typo in mine <> instead of =
=SUMPRODUCT((A3:A10="")*(D3:H10))/(5*(8-COUNTA(A3:A10)))

=SUMPRODUCT(($A$3:$A$10="")*($D$3:$H$10))/SUMPRODUCT(($A$3:$A$10="")*($D$3:$H$10<>"")) will ignore blanks so if there was a blank in d3 it will sum d3:h3 and divide by 9
if you want to ignore 0 or blank then change to

=SUMPRODUCT(($A$3:$A$10="")*($D$3:$H$10))/SUMPRODUCT(($A$3:$A$10="")*($D$3:$H$10<>0))

Originally Posted by thatguytg53

The only other thing I'd ask is if there is a way to make this calculation not average in the blank cells in D3:H26 as zeros in the result?

Sure...

Data Range

	A	B	C	D	E	F	G	H
1	48	----	----	----	----	----	----	----
2
3	42			84	52	56	54	32
4				39	49			56
5	62			63	68	17	73	11
6	2			56	30	44	53	72
7	39			67	71	28	8	92
8	8			20	97	31	76	2
9	70			14	31	2	42	69
10	21			40	64	57	3	69

This array formula** entered in A1:

=AVERAGE(IF(A3:A10="",IF(D3:H10<>"",D3:H10)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.