Counting Blank Cells

Paul Newcome

If it is within the same range, you can use

=COUNTIFS({National Alliance Operation Inquiries Range 1}, OR(@cell <(8), @cell = ""))

If not within the same range, it would be the same syntax as provide earlier.

Beronica Muller

Uggghhh, that will not work.  Now we have figured the actual problem, which is a little different.  I have a column titled days pending from due date. This column calculates the number of days that an item is pending from the submitted date.  The issue is if no date is selected in the due date column, my formula gives a error #invalid data because it is subtracting the due date column from the submitted date when no date is in the due date column. However, I need a formula to count those as well. So basically, they are blanks in the due date field but my days pending from due date column has the error message in those fields.  Did I confuse you enough?  Here is the formula:

=NETWORKDAY([Due Date]3, [Submitted Date]3) * -1

Paul Newcome

You can wrap the formula generating the error in an IFERROR statement to generate a blank. Will that work?

=IFERROR(NETWORKDAY([Due Date]3, [Submitted Date]3) * -1, "")

Beronica Muller

This formula only makes the cell blank and does not count the blank cell in my other formula. I am still trying to send screenshots and unable to.

Paul Newcome

Yes. That removes the error from the source data which allows you to use a formula similar to the previously mentioned formulas to count those that are blank or less than #.

Beronica Muller

Thanks @Paul Newcome.  Management wants to know is there a way to create a formula in smartsheet so that when the due date is empty, we populate a due date based on the priority level selected.  For example in my form, users have the option to select priority levels, urgent, medium or routine. Can we create a formula in smartsheet to calculate the following:

Urgent = today's date

Medium =  tomorrow's date

Routine = 2 days from today's date

Paul Newcome

Yes. I assume you want working days and not weekends, so try this...

=WORKDAY(TODAY(), IF(Priority@row = "Medium", 1, IF(Priority@row = "Routine", 2, 0)))

Beronica Muller

Your assumption is correct. Please see below.

data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAY8AAABLCAYAAACIlAvxAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAABSZSURBVHhe7Z1trB3FecfnYmIrJfVFRKGAfR1epTQhaUslV8JAU6rYfEoEGNu4okGEIkElWmIL+sFGSPAFRGtAgFVEVCoo+Jrr4CqqZGNKy4spRUBk5IRKvNj1Gw1qRK9thHDAp/vb3ef4uXN3z9ndu74XX/9/0ujs7OzOzM488zwzsztzBjoJQYgW2Lt3b5gzZ07uE+L4ZnR0ND9qzsGDB8Pg4GDua8bll18ennnmmdLfppyQ/wohhJgktm/fHm6//fb0d7KYOXNm2LFjR/rbBjIeQggxiWAwHn/88bB///7092gbEIzFoUOH0pHG6tWr01/8M2bMyK9ohoyHEEJMEmY4Pv3009TP79E2IGeeeWZ46623wrJly8ITTzyR/r799tth3rx5+RXNkPEQQohJIDYcs2fPTn+PtgG56qqrwsMPPxy2bdsWPv744/T3/vvvD4sXL86vaIZemIvW0AtzIY7gX5jHhuP8888P119/fXj00Ue7RmPWrFnhmmuuScOMNl6Yw+uvvx6Gh4fDrl270jaK4bjwwgvz0GbIeIjWkPEQ4gjeeNx2223jDIcRG5C77747PYa2jMfRQNNWQghxlCkzHIDfRht23bHAQFjxvxp5CCGE6MuBO2blRxp5CCGEaIDeeQghhOjJwMpfp78aeQghhJgQMh5CCCFqI+MhhBCiNn2Nx/vvvx8GBgbCyy+/nJ/J4NxNN92U+zLwX3bZZbkvg+u8Iz5PHG7Op1cUr+XLzsf3+7AYu/eee+7Jz2Rw/bnnnpv7jlB0/cqVISxbluXNp+ldnD5x+/C4TONwXFF+DMKL6oDzRYy7/rbE/V52GLjlX7PDfsQyUVYG48o/T2PdunWF18f14SGNsvrsRZzXJpCvOK9xuR9rIFfUA89m5WryV1RW9txNIA2TY9Jqu+wsz8RbN27yVVU2Gsv9NKWv8Tj77LPDokWLwp49e/IzIS28c845Jzz77LP5mQwK99JLL02PuYaCZMEL7+RxTz31VHofQuvhvF2D456LL744Dy2GeG688cawadOm/Mz4eKCoIkdGRtJneuSRR/IzGcT13nvvjVNiCAlp3Xrrran/zTdDWL8eJRjSZf+WHtcQr/ktbyZ0Cxcu7Ia99NJL6TPGafnywnFPkQGhDElr7dq1+ZkM8kPZxI0IP9cT3oWkt2WH4cHE/Tg7bIJ/bnO+bsLCxFF8f5r6Uvy1lDsLqao25KZQD7H89YLrkROfVxyyTxh1eyziZYo2biA7r7zySu7LqFNeUwF5NtjHqSqUAXI3EfrK/TSm0rQVBuHFF1/MfSEVrhtuuCE99o198+bN3SXv1157baoITeECG3Jx7uqrr87PFGN7rpQpEgzCOEVYAJVInuJ4UAarVq1Kj+MwDBBKzJQC4cTh00LfL1mSeypgxsfHcdFFF41Lqwi7J27Ajz32WFrGlENRGEbFno348Y8prz9P3Peyw5S/TNz/JO6h1NcujGi2JO7IwtlxWCflySefzM+Mhbw3aZTES4OmvOuCnFFv7777bn7mCJwjDON+LEK5zJ07N22vXuHyPHGnirbPs7YBdThGDlsAI3DGGWeESy65pNJmf7QXDH9Rp0xUJzUeKBkKM3ZWuAiYH2U8//zz6TkMiPVSiIMeAI0UZYVFL9o7pZ9h6AeKmIZbVZHECsnSJZ/kP1ZWGDjusZ47Chol7xkeDmH58txTAYwPgh1DWvDaa6+lv2XQoDEIBuVLnNxP/nwY8Gw0dsKAZ8Fo+x5m2Jy4H2SHXRYl7p+zw1b5x8R5Q9UDU2TIHvlGDvnF+VGkn0ry51EM+LmfsBdeeCH9pd5Nnum8PPDAA+l5b7i5z0aCxIOcmaKz+Pi1NAhDzk2m4jyDKSq718Bv95EH7+cXf4yPyzv//IbFETvLA3lHTnC+g2dy6suFjkeR/FqZ4OIRtA/buXNnfnbstJU/Bl/HxMcxzuIhTz5eyyO6ANmmPVib6gf11rYRO95IjQcCFA+9cNbjIpzCtspCcXEO44AhAYyI9cL27duX/nJNDJWMkfHTYDFMK0F8PwKFIBf1BMsYozATMBY2asKQxdM+gFDxjAgqzgvkhg0hDA2FcMEF+Yk+WJnNnz8//Y3BULFZWS/ioTjlYz1B8kZefWMHU2zkn/LyCiIdCXyYOEYbnj9KnE1j1YQ8WKM214U4/yA7LANlRxy+w8EzIYdxI0eJMmLzcuqVEPFgUAkborJyTG7oDNx8881p2ZusAfdZ54b7TU4oQ46J76677kqNj03PUg9+msfnmWfiWuqBc8RjytHfR+fBTxfxW9TTp66JJ3ZFHal+bboXtGMrF56BcmKU4uE5eB6Ll/qgXoC68GFFbawK1AczBMRBHigjq1f89957b35lPSjHWC80pafcT3MqTVsBlYVRMGECBJTCA3oXRb2TKtDAfOEjiDQ4D+lwHqwBNgFBNgWBAPEsca+J8/TUi3onu3eHZGice6YIphWWu6EPiqaoIaEkeYZ4ZBL+K3GnZodj+EbiMCoNoBxNWZjrwnRYQZn5Ouf9D2XuOwymoGN4Hq9cUeixgirquMQwMrPOD3KNcjKlYobEFKIZX+tIlE2P+DzTUaFcLE4UIfFiYGgrljbTQjyD+flt2pbagLRt6opniOuB/PMcNvUL1IfJGXVho16gXptgMxlgbdX74w7TVNBT7qc5laatAAGiR4TzwkThcT/TWta7Zv4RiiqXcyg035NByVnBIzAIojU4Dy+ZuRfBjRV+GaRnPXdTBKRhz0hc8RwvmHKI89FjwFSI3W+jsRjS7zdPi2G2eChrygBla89AYy3q3VkZW3106T3QqfzVVWVKDJJvcLgxo6Me0Hv2o7G4V1wVP2rzI1Ivtyj2ovcaJutcW1Z/hHn58fXA/aQNtB3yYqMCzpuR8rQ5bdUL0kbGyD9yVTT9DLEMgpVd0zoRxw6Vpq3ApqhwXpgwJDYFZQ2FX4xKUW+4bErKIE0EMTYO1usgbnvRTAPpBYJMQ7T80jPyhgpHI8H1i8to0iYwhvQsY8yYlU1pGZSH9URRcvTk/DPgwOLrS29bNeaLqFYoGuVMABSgn0fvNQXaD+QKmUSBlylJb6goYxuhIDNlih64xpQp+A4EYcRDfKbQ+UXuyVMRpBPXO67taSsgD0w/+d5/DJ05H7e9e4CJ1Ik4Nqg8bYUAIXg4L0w0OJSy9doMhrSxEaChoPRR4L0w4+AbnodGhELu9zkvwm9GxwxJrKgRdq5BKVeBKfQ+ryjGsXLlyjRtPy+P4mG6btyL7AhrwKagynqCftqgL2XTU2XTWRPltMTVLLNeMCViPV2Ip7F6QVl6iMumQ02urT5Q9n4KB6gzU/bIXy9ZZmqRejc5jqexaDO0ExvJ80te/JTPVEFeyHvRqMvajO8QUSYm39SFn9Ky8o0hHv8hjq9T8cWnsvEABMQajkGDQ8hihcZ5eiMIjg1t7eVhWU/NIBzhjBu6h3cRhBOvEb87AeuVMQryDdcTK6NeXHll9t6DtR5VIU3KgoZieUPx0HOLp2p8eeG4x3qLGOKynmCsqHrCyAIjEX+W+5+JyxcN+h7xhCHOn2eHbYB8YHStjCB+N1UGyhA5sVGayWKsJFGAKHvCKQdLC2NBOdtxL1mmnrjG5JS69KME2gztwaa9rA31G4lOBvZesOzdC8+BXFq5UEZWB/z6Mit758F1PH+/65rAVF7VqW1Pq3I/3UmUmqjJ0qWdzooVuedY5ZrEfS877HJq4h7MDiFRoPnRBHkucUWS9sEHmTOmyJ8o905iyMeEJ0qtkwyYOs+sXZv6U1x4SnK87Lvf7SSdkq4/Dp+wXzSC+kuMUe6rR2tyP41I//cpcQcOHOg6GY8GvPFGpzM0lHuOZXztYzS+kx0CyrRp4ysEQ3VrdpiCsj41sVY4jqfI/9lXv9pZOmdOYfihk0/u/CoxID+eP39c+N7h4TRs/5e/3L0+vn/C/tNOkwFpCAYAA1KX1uV+mlBkPPR/Hg1hbyveCeazH8cmTEUzi8I6DGZ/nktc2y/LPZbG19/lhUEITz+dng5XXJH9/vSn2e8k+f92wYKwcePG8PzgYPjSzJml1x9cuDB88sknIT8b8qvCb4aHsy+oGqZfyV8wRSnEZFP0fx4yHmJq4D2OzS3n73Qm2z9w3nnpu6i24jtqfiGmGBkPIRy8pJX4C9Gfvv8kuOHtDWHemuzLDz67s68gvOu1HoKvFJp84cA9E/3CwX/dUSWvXwTsixD/hUdZufvPfNuijXL3eSefVcqcdP2z1aknfy/HxrKRZWHlsytzXwJfkv1OdmjwURPbywghJs4Y43HLplvCxmUbc9/4pfd8dthvbcVEQRmhGKpi19veQ+b4DJa8TmQrk6ON/2zYK/G43PmckU+JedaJQnm0aYjilcTjVrNH8Aysm7BnM5mq8okx1/ApM+WB49juW7d4XVj/i/XhzQ/yb6jZt4u1duwenLMxEe1bbsk9QogJ0TUe9716X5g3OC9ccHr5jn/2TXuZEuO776pbTHi4x9Yy1AHFwTf7ReslbJ0J3+T7HuoXCVYu840/3/UXrT8xCMOg+G3x26BpuXswFmb4ytbReJAhn6bJVL+dhYGV4JaGlYnf2HDJt5aEe7a6uv5h4h7PDoHNLBl93HdffkII0Ziu8Rj55UhY/M1sYVA/6G2iuOnx2zQLBgUl4hU1vVzCcP4899h9dg+/tuoa7B4/crA0rbdJHCwswlBwr0+L+8gTPVtbIVyUZyjKpz2PYXk07BlifFzeFRlclDfKE0VYdZGb4Z8XZ9gz+qkgS5/8YkwZxXDsn8met6zOLF5zFj95t4VvfgFcXarsheT3+AKO/TYly7+9PAz/Yjj3JfDl2HcS5xY4s/bN2RshREO6xmPr7q3hkq/33snTlIlf4UzvmR5+vNLWFKtNuzDF4BUoCoww3wu1FblAGKtc/appeqfW8+QcYVyDImPqg3S4j/23CKNHT744b3GAz3NZPuP7bMdT87Na2O9sa6BAiSt2cfnUwZ7VVvvitxXqxM1qaG/YysBAUX5cX2SseF5W2xMn9UBZGKySxlATZlOCvkybUiRTZRSl588xah6aPZS+u+sSrW6nCLduzT1CiMakxsPmieMpKxSW722iTFAcnqJ9lgDjYHv0oOxRWH7vJZRYP7iP62xKg2kb2wfIGxL22EGxcQw+3SJ8nnvl06eNkeO9Cn4UFoq2isJrQlzuprjNADFV47cpYe8s8uNHG02xNGwkwLOa0bepQdIlfT9l1ATyWyRTE4Gp192ju3NfwumJ+1V2CPY/LHW2lxFCjCc1Hjv+b0fqiUF5Ws/ZXJ0eop+K8DuT1gHFbsocRW977vg/UELZxkbMjJPlpciQ9MsnhgqDhZJj7yPSwI8BwcgUUWfaqgxf7hgN8O90mKrxI40yI9k2/nkwVhPBRosYjqpGuOg5i87t2R/t6Mr/iUTsKBZ5IURFUuNx1slnpZ62sAbtt2X2c9N1YOoJ44Dypbdbpij9Vz4YGxuhsCFimaLvl0+MBdNT/IcJU0akgR8D0mvDOG9szTWdtsJokH9vLDBwfrrPjODRJn4mb9Dq0MRwAM/tn5Xjok7J3NnR+xN29Y04q12RF+K4IzUeNl3V/cyxBeJpKj891Iv4xSkKnp44L9L9tu98pWQKlHCbQsHIYGwI55h0e72M7pVPU2y8cPdfRHHNRN5h1MV2H7X3A4y+/DSVn8ayPNpfmsYjnjLj2wt7VksfpV13NGVwbxPDARhz6pY4cBzbSNTYNborDA0e+evZ8EHi3HoPm66q+jfCQohiui/MFwwtCC/+d3ufgprCtmkOP2ffC1Mo3GOYMvdTU8RlCpS0mDvnHkYFZmxw9JB70S+fTFeRjild/DYlNpnYC2wUNnlB+aKEyTPn/UjErrXywLAYjJgwfkVfivWCMrA4ia9XfZpx4TfG/iDM8m7ODBP5KssbskG6pG958MaQzs/u/bvDlb97ZX4mIfr/9KQ4woIFuUcI0ZxEuaas+Y81nQU/WZD7coq2iJ4Cf6Io022z4/AHV61Kt83uEoVzvPPVV7EenUShFoa36hddEsXeaFdTSEaD+VE9Vmxe0Vn69NLcl1CwFfyCRMTXrMmOnfgLIXrQd0v2ob8b6ryx743MU7RF9BT5/ylRJriicLbETsYqnefuuGNc+Mdf+Uq6bfa2hx4aE5+/vzW/GEMyOsuP6oHRobPQhDHyC/xnCS4n3kpfxkOIavQ1HiO/HEkbYOedd7L/EjAl+bWvZW4K/BiGX8+Y0ff6D5PruBbHMa77PwwF17fqp6woMzFlMOJg5NGF/ydJ7LoHwzEyknsSZDyEqEaR8SjfVZc5dPvCx+bT5c9+i/x2LIQQ0wxtyS6EEKI2fbdkF0IIIaog4yGEEKI2Mh5CCCFqM5C+RRdCCCH6oHceQgghJoS+thKtsXfv3jBnzpzcJ8TxzejoaLqtT1PYgufgwYNhcHAwP/PFQiMPIYQQtZHxEEIIURsZDyGEELWR8RBCCFEbGQ8hhBC1kfEQQghRGxkPIYQQtZHxEEIIUZuBPXv2aJGgaA0tEhQiY7ovEtQKc9EaWmEuxBG0wlwIIYSIkPEQQghRGxkPIYQQtZHxEEIIURsZDyGEELWR8RBCCFEbGQ8hhBC1kfEQQghRGxkPIYQ4zvjscAgPvXU4/PGGz8Pcn3yW/uLnfFVkPIQQ4jgCA/H9n30e/mbr4fDmh50weiikv/iv+JfPKxsQGQ8hhDiO+Pvth8NL+7Jdqf76D2eFA381GH52xUnhxMQa/NueThpeBRkPIYQ4jlj/TmY4zhw8ISw668Rw+tr9Ye5vnxAumnNiet7C+6GNEUVraGNEIY4wlRsjXnfddeGjjz7KfWP59z95LHz2pZNyXwjfOGVG2LLkpHDxUwfDztHDYXBmCHt+lBmSXmjkIYQQ0wwMxymnnBLWrFkTtmzZMsb9/tDs/KqMp3/wW+EvNn+SGg445+SB9LcfMh5CCDENWb16dbjzzjvD9u3b8zMZf/bNZGjh+PY/HAibdvwm94Ww5LwqxiOE/wfbJUaQDs4pMAAAAABJRU5ErkJggg== data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAATMAAABCCAYAAAA7UTGOAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAAAS5SURBVHhe7d1PLjRBHMbxnvcaRASnECwscAALrGw5ABsXIPYSKyssHAALC6ycAQsZ5/D2U/pHTXXPdLe380788v0klanu6T/Vk/TzVlWPd3ofuQzowPv7ezYxMVEsAf/Xn+IVAH41wgyAC4QZABcIMwAuEGYAXOj1+32eZqIzPM3EuPDVDHSGr2ZgnBhmAnCBMAPgAmEGwAXCDIALQ8Ps6OgoFNnd3c0uLy+/6mtra6EeS9f3er2wLjU3N/d1LL1qu7TYeVN6r+qY2j49hkpVO7Uu3iY9V/xeXIZds95LPT4+lvZXsesWu/Zh0n2tVLUDwIgwu7u7y+bn50P99fU1m5ycDPU2Tk5Owo1dRw9Urby8vGT7+/ul/XTzr66uhmNWmZ2dHTiOys3NzUBYWRDG2+hcaaBdXFwMbKNyfX1dvPtNbVGb4pCK6Vps/4eHh2xzc7PR52GatgPAiDB7fn7OFhYWQl2hYPU2dKNvb28XS83MzMyE/c7Pz4s1n87OzsKxRoVHamdnJ4Syub29LbVHgXF6elosNWfhquOpbXXs8+v3++EVQLdKYWbDGfUqrG7r1UNr4+DgILwOGzaOMj09XdQ+e4YK1I2NjcbhUUVDXGuT0TEV3G1ZuGp/ta3us7EembYH0L1SmGkoo96KejVpXb2mtnTTayjXNAh10yscbIgrV1dXoQ3SNDxEw8Dl5eViKQtDtDikq+bfRMNB28ZK3BuMw1XUNrUxpaGv7b+4uBiGmm3UtQPAt8ph5tvb21fP6P7+PltaWgr1n9DwSsOx4+PjYk1ZfLPqpj88PBwY1moYuLW1VSx9hkd6vDikrGi7vb29YotPNvekkFbYabsmc2ZxjyoOV1HbFNipeM5MdV1bmzCqaweASH6DDNCqYSW/IT/yoPnIw6nY+lt+c4ditH3eEwl17WfLeW/lI79Jw3q9av0o2sfOnxajNum4xo6r89ZJ26C6tW8YncvaEBfbz9qcnl+fj312ddceH++30N/5AuNSOcy017SuYebU1FTlHJMm1+N5rpj2U28rnXxvQg8CtK+1wYoM6+Wo96J9NMwzGr6qF5ayp7RthsGStkc9tZ/O5QH4d6Uw001tIaAbV0PEmIJCQ6Z4aKZQ0br19fViTZkN97RdGxoKxvNnpi48dD5dh82Ladiq5fR7WgpLXWPT+UBtv7KyUix901Bz1Fye1utafhLoAOqVwuzp6enrZtXXCKpucvVENEdkc1OaqFZI1QVC256LAlMBVPW1kLrwEJ1PAWLBaz1Ka7f11NLvblVNvKtYIMXzd8bCMn4QoGXbV3X1FtM5r/j4KvE/EsPaAaCM/wIIneG/AMI4VT7NBIDfhjAD4AJhBsAFwgyAC4QZABcIMwAuEGYAXCDMALjAjwCjU3xpFuPCXwCgM/wFAMaJYSYAFwgzAC4QZgBcIMwAuECYAXCBMAPgAmEGwAXCDIALhBkAFwgzAC4QZgBcIMwAuECYAXCBMAPgAmEGwAXCDIALhBkAFwgzAC4QZgBcIMwAuECYAXCBMAPgAmEGwAV+BBid4nczMS78CDA6w48AY5wYZgJwgTAD4AJhBsAFwgyAC4QZABcIMwAuEGYAXCDMADiQZX8Brd6WYEte0Z8AAAAASUVORK5CYII=

Paul Newcome

You will need to make sure that the column name (Priority) matches. You also have an extra equals sign in front of "Medium". Double checking the column name and removing one of those equals signs should get it operational.

Beronica Muller

@Paul Newcome apparently, I am still doing something wrong. Please see below as I an receiving the #UNPARSEABLE error.

Paul Newcome

You should not have the spaces between the column name and @row. @row replaces the row number.

Beronica Muller

Thanks for pointing that out. I tried it with and without a space and received the same error message.

Paul Newcome

Can you copy/paste the actual formula from the sheet to here?

Beronica Muller

Sure thing....

=WORKDAY(TODAY(), IF(Priority@row = "Medium - 24 hours", 1, IF(Priority@row = "Routine - 48 response", 2, 0)))

Paul Newcome

Ok. I can't see anything out of place, but it looks like it isn't picking up on the column name for whatever reason Let's give this a try...

Manually retype the formula. Whenever you get to the Priority column references, click on a cell in the Priority column (any cell will do). Backspace to remove the row number and enter the @row.

See what happens when we do that. Does anything change?