Want A Zero answer to be a blank cell

[Morgo]

Hi,

I need some help with a formula, i am using a COUNTIF formula to count specific letters in a column but i need it to leave the answer cell blank if it comes back zero?

So far my formula is as below but how do i get a zero returned value to stay blank?

So in cell E32 i have;
=COUNTIF(C10:C16,"D")

if there are no D's in this range it currently displays a 0 but i want to leave it blank, can anyone help me out here, i tried to add an IF on the end but i keep getting the error message.

Thanks

 

[Asheesh]

Below are the couple of ways to accomplish this task

1) IF(COUNTIF(C10:C16,"D")=0,"",COUNTIF(C10:C16,"D"))

2) Set custom format of Cell E32 to General;General;;

 

[jayalaxmi]

try this

=IF(COUNTIF(C10:C16,"d")=0,"",COUNTIF(C10:C16,"d"))

 

[Khalid NGO]

Hi Morgo,

adding to Asheesh (and Jaya).

Asheesh's 2nd solution should be preferred.
as this does not required multiple calculations, and give what you want with just:
=COUNTIF(C10:C16,"D")

Regards,

 

[Morgo]

Thanks very much all of you it works a treat now,

I do have a question in regards to the 2nd solution, i don't understand what you mean by General;General;; for the custom format?

I am also trying to do the same with multiple criteria but i am only getting back a blank cell all the time!

=IF(COUNTIFS(J20:J22, "N", J20:J22, "S"), COUNTIF(J20:J22,"N"),"")

Any ideas why?

 

[Somendra Misra]

Hi,

Can you upload a sample file with some raw data and sample output. The formula you posted above is logically wrong usage of COUNTIFS functions.

COUNTIFS works on AND logic, simply you are telling COUNTIFS to check for N in range J20:J22 and also S in the same range. Now a cell can have either N or S but not both. So COUNTIFS will always return 0, which IF will treat as FALSE and keeping the cell blank by outputting a null string "", as per your formula.

This has nothing to do with the custom format of the cell as @Asheesh suggested.

Regards,

 

[Morgo]

Unfortunately due to our business privacy clause i cannot upload a sample so i will do my best to describe what i'm trying to do & why;

My spreadsheet is to track our crew numbers according to predicted attendance based on our work roster, so the D is dayshift, N is Nightshift if no one has put in annual leave i should see either a D or N in the cell, if they are approved for annual leave it will show an A, if a crew leader is away on leave we need someone to step up into that role hence the S. If you look below at the clipping we are on Nightshift Mon & Tue but 3 people are off on Leave & then on the weekend we have 3 people off but also a team leader so the person with an S takes on the team leader role for those shifts. Everyone with a D or an N or S will be considered as attending work for that shift & those are what i need to count per shift. I hope i have described it well enough, it's probably a very easy one but it has got me stumped!

 

[Somendra Misra]

What is F01 & E01?

The numbers below are correct output as per you?



Regards,

 

[Morgo]

So the section where i have 2 A's & 1 S is F01 for Fitters & the section below with 3 cells & it contains 1 A is the E01 for Electricians.

So on Friday, Saturday & Sunday it should show 4 under F01 not 3 because the S is considered here & not absent. I want it to count the S as it would the N or the D. So in the code above that the guys have helped out with all i have done is simply substitute the D with N for the night shift but i need to count an S as if it is a D or an N.

 

[Somendra Misra]

So if you are counting N or D separately than any of the above formula should solve your query.

The only problem I am looking at is S, as you are saying that they can be N or D?

Regards,

 

[Morgo]

That's right, at the moment it isn't looking for an S to count it & when i try to add it to the formula i either get it wrong or as per my example above get it really wrong.

So when the formula is counting the N's i want it to also count The S's in the count as if they are an N & the same for when it is counting D's.

 

[Khalid NGO]

I do have a question in regards to the 2nd solution, i don't understand what you mean by General;General;; for the custom format?

Hi,
To achieve this, Press Ctrl+1 on your cell.
Go to Number Tab
Click on Custom
at Type: copy/past or type General;General;;
click ok and you are done.


Regards,

 

[Morgo]

OK so i am getting closer, but i still need some help. I can now get an answer but the answer still isn't right.

=IF(AND(COUNTIF(AP10:AP16,"D"),COUNTIF(AP10:AP16,"S")),COUNTIF(AP10:AP16,"D"),"")

The above formula is still returning a value of 3 instead of 4, it still isn't counting the "S" can anyone tell me why?

 

[Asheesh]

Hi Morgo - try the below formula

Opt 1) SUM(COUNTIF(AP10:AP16,{"D","S"}))

Opt 2) SUMPRODUCT(--(AP10:AP15={"D","S"}))