• 590K Members
• 3,703 Online
• 715K Conversations
SOLVED

Highlighted
New Contributor

# Excel Forumla to exclude empty cells.

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
Highlighted
Solution

# Re: Excel Forumla to exclude empty cells.

@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

Highlighted

# Re: Excel Forumla to exclude empty cells.

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.

Highlighted

# Re: Excel Forumla to exclude empty cells.

@ulken2019 , please try following to exclude blanks

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

Highlighted

# Re: Excel Forumla to exclude empty cells.

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.

Highlighted

# Re: Excel Forumla to exclude empty cells.

@Peter Bartholomew

Good note!

Thanks for that!

Highlighted

# Re: Excel Forumla to exclude empty cells.

That worked, Thank you!

Related Conversations
Excel cell formatting
snojoe10 in Excel on
0 Replies
Corrector automático
jorgf2047 in Excel on
0 Replies
Problem when opening Excel
LarryC1940 in Excel on
0 Replies