cannot get totals if less than 6 mos

Outline

New Member
Joined
Mar 25, 2009
Messages
20
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
 

Some videos you may like

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
Joined
Jul 15, 2008
Messages
1,348
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
Joined
Sep 10, 2008
Messages
5,196
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,358
Messages
5,547,464
Members
410,795
Latest member
santa12345
Top