I have cells A1 through A10 which can contain numbers or text. I want to be able to sum this range while always ignoring the ones with text so as to eliminate the error when summing.
I used the formula below in a schedule I am creating. The schedule reads (Time Out - Time In) - (Lunch out - Lunch in) to give me a total number hours worked for each employee. The schedule is on one tab and the data is on another. I was originally getting the ### error if the cells were blank (person not working that day). When I sum all the hours worked for that person, that month, It looked like the sum was correct, I was getting a number close but not correct. It was deducting 30 minutes from each cell that had the error. I used the formula below which corrected the ### error and sums correctly however, it does not subtract the lunch break. My current formula before attempting the one below is. =(DECEMBER!N8-DECEMBER!M8)-(DECEMBER!$AB$7-DECEMBER!$AA$7). I need to know how to adapt the new formula =IF(AND(ISNUMBER(DECEMBER!K6),ISNUMBER(DECEMBER!J6)),DECEMBER!K6-DECEMBER!J6,"") to also subtract the lunch break
___________________________________________________
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.