Treat Missing Values as 0?

pceleri

New Member
Joined
Apr 2, 2019
Messages
7
I've been working for sometime on creating an accurate payroll environment within excel, and I'm finally close, but ran into an issue I could use the expertise of this forum with. The workbook contains sheets for each month (January to December) separated by Quarterly sheets that populate from the appropriate monthly sheets, and a YTD sheet that sums the info from the Quarterly sheets. What I'm finding is that on the YTD sheet I'm getting missing value errors for the months that haven't come (or been entered yet). Is there a way to have excel treat these 0 value cells as a 0 and add up what is currently there? Here is an example of the code - [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

=IFERROR(VLOOKUP(A22, January, 131, 0)+VLOOKUP(A22, February, 131, 0)+VLOOKUP(A22, March, 131, 0), 0)

For example, lets say that I have entered January payroll, February and March are blank (well not blank as many cells contain a formula waiting on hours worked to be entered), and the Quarter1 sheet will give a Missing Value error, or rather just display 0, instead of showing the value for January.

Thanks in advance
[/FONT]
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,617
Office Version
  1. 2007
Platform
  1. Windows
Try:

=IFERROR(VLOOKUP(A22, January, 131, 0), 0) + IFERROR(VLOOKUP(A22, February, 131, 0),0) + IFERROR(VLOOKUP(A22, March, 131, 0), 0)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,617
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,888
Messages
5,621,426
Members
415,839
Latest member
Pollydooner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top