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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

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,109,503
Messages
5,529,248
Members
409,857
Latest member
KailuaTown
Top