cannot get totals if less than 6 mos

[Outline]

this is a formula to add data for mos NOV through APR

=VLOOKUP(B7,NOVTERM!$1:$65536,4,0)+VLOOKUP(B7,DECTERM!$1:$65536,4,0)+VLOOKUP(B7,JANTERM!$1:$65536,4,0)+VLOOKUP(B7,FEBTERM!$1:$65536,6,0)+VLOOKUP(B7,MARTERM!$1:$65536,6,0)+VLOOKUP(B7,APRTERM!$1:$65536,6,0)

I get data if all 6 months are populated, otherwise the output is #N/A. How can I get the sum when only 1, 2 3, 4 or 5 mos have data?


Thanks,
Outine
drubc@allstate.com

 

[PCL]

For each month you could change formula to
from

VLOOKUP(B7,APRTERM!$1:$65536,6,0)

to

IF(iserror(VLOOKUP(B7,APRTERM!$1:$65536,6,0)
),0,VLOOKUP(B7,APRTERM!$1:$65536,6,0)
)

NOT TESTED

 

[Weaver]

If you're pulling just numeric data, try swapping for sumif

VLOOKUP(B7,APRTERM!$1:$65536,6,0)

I'm assuming you're looking at the whole sheet

swap for

SUMIF(APRTERM!$A:$A,B7,APRTERM!$F:$F)

AND SO ON

NOTE: The second range is ($F:$F) is the one you should change according to which column you're after.