isna issue

[Peter1973]

I am using the formula :

=ROUND((VLOOKUP($D7,SUN!$O$7:$V$800,8,FALSE)+VLOOKUP($D7,MON!$O$7:$V$800,8,FALSE)+VLOOKUP($D7,TUE!$O$7:$V$800,8,FALSE)+VLOOKUP($D7,WED!$O$7:$V$800,8,FALSE)+VLOOKUP($D7,THU!$O$7:$V$800,8,FALSE)+VLOOKUP($D7,FRI!$O$7:$V$800,8,FALSE)+VLOOKUP($D7,SAT!$O$7:$V$800,8,FALSE))/7,0)

Is there a quick way to chneg this so if any of the lokups canot be found it does not return #N/A? or do i have to use an isna for every lookup?

any hepl appreciated.

 

[Jonmo1]

Do you mean if any of the vlookups are N/A, then that vlookup only should be replaced with a 0, but the rest continue their calcs?
If that's your goal, then yes you have to do the IF(ISNA on each vlookup.

But if you want to make the entire formula return 0 if any 1 or more of the vlookups is N/A, then you can just put the whole thing in 1 IF(ISNA.

I would actually recommend putting each vlookup into it's own cell, then Sum them using
=SUMIF(A1:G1,"<>#N/A")

 

[Firefly2012]

You could consider doing it like this (although note there are reasons why you might not - for example if you have bazillions of these formulas to create, oh and also 'cos it's a pig to debug):

=ROUND(SUMPRODUCT(SUMIF(INDIRECT(TEXT(ROW(INDIRECT("1:7")),"ddd")&"!O7:O800"),D7,INDIRECT(TEXT(ROW(INDIRECT("1:7")),"ddd")&"!V7:V800")))/7,0)