Sum an average when error message #VALUE!

[Dan_K_2014]

Hello all,

There are 2 rows with formulas. For example Row 6 and 7 from columns E to P and Total in Q.
In Q6 should show an average of all in row 6.
Formulas in E6 : P6 and E7 : P7 are from January to December pulled from another sheets (Data files).
From N6 to P6 and N7 to P7 there are no data yet (October- December- no data yet).
In N6 there is a formula: .=IFERROR(COUNTIFS(M_10!$A:$A,$N$5,M_10!$H:$H,">="&N5,M_10!$H:$H,"<="&N4)/N7,"").
In N7 there is a formula: .=IFERROR(SUMPRODUCT(--(M_10!$B$2:$B$50000=$B$2),--(MONTH(M_10!$A$2:$A$50000)=MONTH(N$5)),--(M_10!$M$2:$M$50000=1),M_10!$N$2:$N$50000),"").

All formulas work OK, except of formula in Q6 in which I want want just to SUM average from E6 to P6 (January: December),
I tried different options but all of them show an error: #value! OR just empty cell.
I tried these:
.=SUMIF(SUM(IF(ISERROR(E6*E7+F6*F7+G6*G7+H6*H7+I6*I7+J6*J7+K6*K7+L6*L7+M6*M7+N6*N7+O6*O7+P6*P7),"",E6*E7+F6*F7+G6*G7+H6*H7+I6*I7+J6*J7+K6*K7+L6*L7+M6*M7+N6*N7+O6*O7+P6*P7),OR("<>#VALUE!"))

In order to SUM an average in Q6 I need to devide total of E6*E7...P6*P7/ Q7.

Thank you i advance.

 

[Chihiro]

Can you upload sample?

Also, to sum range & multiply it's easier to use SUMPRODUCT.

For this portion:
(E6*E7+F6*F7+G6*G7+H6*H7+I6*I7+J6*J7+K6*K7+L6*L7+M6*M7+N6*N7+O6*O7+P6*P7)

It can be.

SUMPRODUCT(E6:P6,E7:P7)

 

[Dan_K_2014]

SUMPRODUCT(E6 : P6,E7 : P7) In Q6 gives almost correct number.
But now another problem in Row E12 : P12.
I have this formula in Cell E12=IFERROR(COUNT(M_1!$L:$L)-E7*E6-E9*E8-E11*E10,"")/E13. It works while I have data, but when there is no data for example in Cell L12 =IFERROR(COUNT(M_8!$L:$L)-L7*L6-L9*L8-L11*L10,"")/L13 the formula gives an error #VALUE!.

I could not upload a file.

 

[Chihiro]

Some question.

Which cell has no data in your second example? Is any of the cells evaluating to error?

Also, try evaluating formula to see where the error is occurring.

 

[Dan_K_2014]

There is no data now from August to December (columns L : P). The error starts when I am putting a division by /L13.

 

[Chihiro]

Does one of the cell contain error or text?

 

[Dan_K_2014]

Yes. and I used to hide those error iferror.

 

[Chihiro]

Ah, then try replacing "" with 0.

Make sure there's value in L13 or you will get #DIV/0!

If you just want the blank returned instead of error...
=IFERROR((COUNT($L:$L)-L7*L6-L9*L8-L11*L10)/L13,"")

 

[Dan_K_2014]

I put like this : .=IFERROR(IFERROR(COUNT(M_8!$L:$L)-L7*L6-L9*L8-L11*L10,"")/L13,"") and it is working now. Needed 2 IFERROR-)
Thank you for your help, Chihiro!!!