# cannot get totals if less than 6 mos

#### Outline

##### New Member
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

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### PCL

##### Well-known Member
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

##### Well-known Member
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.

Replies
10
Views
139
Replies
0
Views
31
Replies
3
Views
41
Replies
10
Views
279
Replies
2
Views
43