SOLVED

Excel Forumla to exclude empty cells.

Copper Contributor

All,

 

I am using the following formula on my excel worksheet and it is including blank cells.

 

I was wondering if anyone could assist on helping me modify it to ignore/disregard the blank cells.

 

="# of Other Tech Tickets:"&COUNTIF(A5:A279, "<>Tech Name")

 

In the above formula, I'm getting the following result. I wish I was efficient as the number below.. In actually I only worked 27 total, 14 of my own an 13 of other techs (different names), so I'm trying to exclude my name (which I had accomplish, I believe) but it's counting blank/empty cells.

 

# of Other Tech Tickets:261

 

Any input is greatly appreciated! I can't seem to figure this out.

6 Replies
best response confirmed by ulken2019 (Copper Contributor)
Solution

@ulken2019

 

Hi,

 

The best way to do so is to use COUNTIFS (with s) as below instead of COUNTIF, which allows you to add more than one criteria.

="# of Other Tech Tickets: "&COUNTIFS(A1:A279,"<>Tech Name",A1:A279,"<>"&"")

 

Hope that helps

@ulken2019 

 

Hello,

 

If I understand correctly, you want to make sure that the "0's" are not counted bc it can have an effect on the tasks completed. Please see the link below. 

https://www.google.com/search?q=how+to+exclude+empty+cells+in+excel&rlz=1C1GCEU_enUS826US826&oq=how+...

@ulken2019 , please try following to exclude blanks

="# of Other Tech Tickets:"&(COUNTIF(A1:A275, "<>Tech Name")-COUNTBLANK(A1:A275))

@ulken2019 

I would vote for @Haytham Amairah's solution other than observing that the final parameter of COUNTIFS, "<>"&"", reduces to "<>" and can be used in that form.

@Peter Bartholomew

 

Good note!

Thanks for that!

@Haytham Amairah 

 

That worked, Thank you!

1 best response

Accepted Solutions
best response confirmed by ulken2019 (Copper Contributor)
Solution

@ulken2019

 

Hi,

 

The best way to do so is to use COUNTIFS (with s) as below instead of COUNTIF, which allows you to add more than one criteria.

="# of Other Tech Tickets: "&COUNTIFS(A1:A279,"<>Tech Name",A1:A279,"<>"&"")

 

Hope that helps

View solution in original post