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!