Hi Ashish, That resulted in zero and the result should be 1. Because there is one blank cell that meets the criteria of the shift being 09:00 to 19:00 from Column G.
I figured this one out and I believe it is working: =COUNTIFS($E$5:$E$34,"=17",$G$5:$G$34,">=09:00:00",$G$5:$G$34,"<=19:00:00",E 5:E34,"")+COUNTIFS($E$5:$E$34,"",$G$5:$G$34,">=09:00:00",$G$5:$G$34,"<=19:00 :00") This formula counts and looks for criteria of =17, then count looks for the time range of >09:00 to <=19:00 and then adds that number to any blank cells from those two criterias. Thank you! Your help is appreciated J ~Susan From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of ashish koul Sent: Thursday, March 31, 2011 7:24 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Need to count blank cells with CountIF formula see the forumula in yellow . On Thu, Mar 31, 2011 at 11:37 PM, <sunni...@gmail.com> wrote: Hi, I have this formula, =COUNTIFS($E$5:$E$17,15,$G$5:$G$17,">=09:00:00",$G$5:$G$17,"<=19:00:00"), which returns to me a count total based on two criterias, but I also need to add in this same formula how many blank cells there are, from E5:E17. I did this formula: =COUNTIFS($E$5:$E$17,17,$G$5:$G$17,">=09:00:00",$G$5:$G$17,"<=19:00:00")-COU NTBLANK(E5:E17) When I use - sign the answer becomes -2 When I use + sign the answer become 2 The actual number should be 1, which is one blank cell between criteria 09:00:00 and 19:00:00, without counting the blank cells the answer is 0 or zero. How can i tell excel to add on the blank cells to my formula, =COUNTIFS($E$5:$E$34,15,$G$5:$G$34,">=09:00:00",$G$5:$G$34,"<=19:00:00") I tried this but it results with #NAME?, =COUNTBLANK(IFS($E$5:$E$17,17,$G$5:$G$17,">=09:00:00",$G$5:$G$17,"<=19:00:00 ")) Thanks! ~Susan -- ---------------------------------------------------------------------------- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com <http://www.excel-macros.blogspot.com/> 4. Learn VBA Macros at http://www.quickvba.blogspot.com <http://www.quickvba.blogspot.com/> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com <http://exceldailytip.blogspot.com/> To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Regards Ashish Koul <http://akoul.blogspot.com/> akoul.blogspot.com <http://akoul.wordpress.com/> akoul.wordpress.com My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830> P Before printing, think about the environment. -- ---------------------------------------------------------------------------- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
