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]
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Try:

=IFERROR(VLOOKUP(A22, January, 131, 0), 0) + IFERROR(VLOOKUP(A22, February, 131, 0),0) + IFERROR(VLOOKUP(A22, March, 131, 0), 0)
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

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
Back
Top