SOLVED
Home

Excel Forumla to exclude empty cells.

%3CLINGO-SUB%20id%3D%22lingo-sub-730530%22%20slang%3D%22en-US%22%3EExcel%20Forumla%20to%20exclude%20empty%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-730530%22%20slang%3D%22en-US%22%3E%3CP%3EAll%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20the%20following%20formula%20on%20my%20excel%20worksheet%20and%20it%20is%20including%20blank%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20wondering%20if%20anyone%20could%20assist%20on%20helping%20me%20modify%20it%20to%20ignore%2Fdisregard%20the%20blank%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%22%23%20of%20Other%20Tech%20Tickets%3A%22%26amp%3BCOUNTIF(A5%3AA279%2C%20%22%26lt%3B%26gt%3BTech%20Name%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20above%20formula%2C%20I'm%20getting%20the%20following%20result.%20I%20wish%20I%20was%20efficient%20as%20the%20number%20below..%20In%20actually%20I%20only%20worked%2027%20total%2C%2014%20of%20my%20own%20an%2013%20of%20other%20techs%20(different%20names)%2C%20so%20I'm%20trying%20to%20exclude%20my%20name%20(which%20I%20had%20accomplish%2C%20I%20believe)%20but%20it's%20counting%20blank%2Fempty%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%23%20of%20Other%20Tech%20Tickets%3A261%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20input%20is%20greatly%20appreciated!%20I%20can't%20seem%20to%20figure%20this%20out.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-730530%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eexclude%20blank%20cells%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eformula%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-730598%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Forumla%20to%20exclude%20empty%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-730598%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F369060%22%20target%3D%22_blank%22%3E%40ulken2019%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20best%20way%20to%20do%20so%20is%20to%20use%20COUNTIFS%20(with%20s)%20as%20below%20instead%20of%20COUNTIF%2C%20which%20allows%20you%20to%20add%20more%20than%20one%20criteria.%3C%2FP%3E%3CPRE%3E%3D%22%23%20of%20Other%20Tech%20Tickets%3A%20%22%26amp%3BCOUNTIFS(A1%3AA279%2C%22%26lt%3B%26gt%3BTech%20Name%22%2CA1%3AA279%2C%22%26lt%3B%26gt%3B%22%26amp%3B%22%22)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-730601%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Forumla%20to%20exclude%20empty%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-730601%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F369060%22%20target%3D%22_blank%22%3E%40ulken2019%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20understand%20correctly%2C%20you%20want%20to%20make%20sure%20that%20the%20%220's%22%20are%20not%20counted%20bc%20it%20can%20have%20an%20effect%20on%20the%20tasks%20completed.%20Please%20see%20the%20link%20below.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.google.com%2Fsearch%3Fq%3Dhow%2Bto%2Bexclude%2Bempty%2Bcells%2Bin%2Bexcel%26amp%3Brlz%3D1C1GCEU_enUS826US826%26amp%3Boq%3Dhow%2Bto%2Besxclude%2Bempty%2Bce%26amp%3Baqs%3Dchrome.1.69i57j0l5.6338j0j7%26amp%3Bsourceid%3Dchrome%26amp%3Bie%3DUTF-8%23kpvalbx%3D1%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.google.com%2Fsearch%3Fq%3Dhow%2Bto%2Bexclude%2Bempty%2Bcells%2Bin%2Bexcel%26amp%3Brlz%3D1C1GCEU_enUS826US826%26amp%3Boq%3Dhow%2Bto%2Besxclude%2Bempty%2Bce%26amp%3Baqs%3Dchrome.1.69i57j0l5.6338j0j7%26amp%3Bsourceid%3Dchrome%26amp%3Bie%3DUTF-8%23kpvalbx%3D1%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-730604%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Forumla%20to%20exclude%20empty%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-730604%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F369060%22%20target%3D%22_blank%22%3E%40ulken2019%3C%2FA%3E%26nbsp%3B%2C%20please%20try%20following%20to%20exclude%20blanks%3C%2FP%3E%3CP%3E%3D%22%23%20of%20Other%20Tech%20Tickets%3A%22%26amp%3B(COUNTIF(A1%3AA275%2C%20%22%26lt%3B%26gt%3BTech%20Name%22)-COUNTBLANK(A1%3AA275))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-731086%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Forumla%20to%20exclude%20empty%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-731086%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F369060%22%20target%3D%22_blank%22%3E%40ulken2019%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20vote%20for%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E's%20solution%20other%20than%20observing%20that%20the%20final%20parameter%20of%20COUNTIFS%2C%20%3CSTRONG%3E%22%26lt%3B%26gt%3B%22%26amp%3B%22%22%2C%3C%2FSTRONG%3E%20reduces%20to%20%3CSTRONG%3E%22%26lt%3B%26gt%3B%22%3C%2FSTRONG%3E%20and%20can%20be%20used%20in%20that%20form.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-731636%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Forumla%20to%20exclude%20empty%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-731636%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EGood%20note!%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3EThanks%20for%20that!%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-736197%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Forumla%20to%20exclude%20empty%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-736197%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20worked%2C%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E
ulken2019
New 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
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!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
18 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies