If Formula with Sum to replace 0 with Hyphens

[Manny74]

Hi All, I am trying to create a formula to replace a blank or zero with a hyphen for each quarter...I feel as though I am close to figuring this out.
for 1st Qtr, I typed =IF(C3+D3+E3=0,"-",SUM(C3:E3)) and it seemed to work..
But couldn't I type =IF(SUM C3:3=0,"-",SUM(C3:E3)) ?

COLUMN

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

ROW 2

Jan

Feb

Mar

1st Qtr

Apr

May

June

2nd Qtr

July

Aug

Sept

3rd Qtr

Oct

Nov

Dec

4th Qtr

ROW 3

Complaints

-

0

0

0

 

[Fluff]

You're welcome & thanks for the feedback

 

[Manny74]

Hello All,
I have another question...

So I have this formula for 1st Qtr, so that if there are zero complaints, then 1st Qtr will be zero....

But I'm also trying to calculate the number of complaints divided by total surveys, so I can get a percentage for each category

I used this formula--- IF(COUNT(F3/F8)=0,"-",F3/F8)

But if there are zero complaints for Jan, Feb & March, I'd like to see % as zero.....How should I adjust my formula?

If Jan Feb Mar are blank I'd like % to have a hyphen, and if Jab Feb Mar are zeros, then I'd like % to be zero....

COLUMN	B	C	D	E	F	G
ROW 2		Jan	Feb	Mar	1st Qtr	%
ROW 3	Complaints	0​	0	0	0	-
ROW 4	Grievances				-	-
ROW 5	Positive Feedback	0​	0	0	0	-
ROW 6	Neutral Feedback				-	-
ROW 7	Negative Feedback				-	-
ROW 8	Total Surveys	0​	0​	0​	0	-

 

[Scott Huish]

I'm not sure why you used COUNT, that just counts how many numbers; F3/F8 unless it was #DIV/0 would only ever be 1 number.

=IF(SUM(C3:E3),IF(F3/F8,F3/F8,"-"),0)

 

[Manny74]

Hi Scott, I think I tried a recommended formula and it wasn't working...So then this was recommended: =IF(COUNT(C3:E3)=0,"-",SUM(C3:E3)) and it seemed to work.....

So now when I enter zero complaints for Jan, Feb & March, in 1st Qtr I get zero, which is what I want, and if I leave Jan, Feb & March blank, 1st Qtr is a hypen, which is what I want....so it seems to work...

So I tried your formula, IF(SUM(C3:E3),IF(F3/F8,F3/F8,"-"),0), and it seems to work....

But the % (column G) is 0% no matter what, even when the cell is blank....Is there a way to have G3 populate a hyphen when Jan Feb & March are zero?

Maybe not, but I thought I would ask...

Thanks for your patience...

COLUMN	B	C	D	E	F	G
ROW 2		Jan	Feb	Mar	1st Qtr	%
ROW 3	Complaints	0​	0	0	0	0%
ROW 4	Grievances				-	0%
ROW 5	Positive Feedback	0​	0	0	0	0%
ROW 6	Neutral Feedback				-	0%
ROW 7	Negative Feedback				-	0%
ROW 8	Total Surveys	0​	0​	0​	0	0%

 

[Manny74]

I would like the G column (%) to have a hyphen when the cells for Jan, Feb & March are blank...

Also I would like the G column (%) to have a 0 in the cell when Jan, Feb & March is 0.....

I used IF(SUM(C3:E3),IF(F3/F8,F3/F8,"-"),0)

And so when there are zeros for Jan, Feb & March, the % cell is still a hyphen..... Is there a way to have it 0%?

COLUMN	B	C	D	E	F	G
ROW 2		Jan	Feb	Mar	1st Qtr	%
ROW 3	Complaints	0	0	0	0	-
ROW 4	Grievances				-	-
ROW 5	Positive Feedback	0	0	0	0	-
ROW 6	Neutral Feedback				-	-
ROW 7	Negative Feedback				-	-
ROW 8	Total Surveys	0	0	0	0	-

 

[Fluff]

How about
=IFERROR(F3/F8,"-")

 

[Manny74]

Hi tried =IFERROR(F3/F8,"-")...It's a valid formula...
When Jan Feb & March are blank, then the % cell has a hyphen, which is great!
But what if I also want the % cell to populate zero (0%), if Jan Feb & March are zeros?

 

[Fluff]

As long as F8 isn't 0 then it will work.

 

[Manny74]

Gotcha!
But what if F8 is blank (0 surveys) ?

 

[Fluff]

You will get a #DIV/0! error without the IFERROR function.