Formula to display dash vs zero based on cells

[Larry of Oz]

Hello gurus, I'm hoping someone can help with a solution that is escaping me no matter what I seem to try. I've found similiar problems on other threads, but not quite the right solution.

I have an Excel workbook to track the rollout of a project. On one worksheet are the locations of the project in rows, and the months in columns. If the project has NOT commenced, there is a dash (hyphen) in the column cell. for that month. If the project HAS commenced, a number referring to users is in the column cell for that month (note the number could be zero). A 'Locations' screenshot is attached.

On my summary worksheet, to be used for reporting, I have been trying to come up with a formula that will pull in a total for the number of users by location per quarter OR enter a dash if each of the three quarter months shows a dash indicating the project hasn't commenced. I originally had the following formula:

=IF(SUM(Eastern!N5:P5)=0,"-",SUM(Eastern!N5:P5))

As luck would have it we have rolled out to a location that has had some issues, so though the project has commenced, the usage on the source document shows 0 (zero). Zero indicates project commenced but no usage. So in this case I need it to actually show a zero, not a dash. I tried something like the below but that didn't work either. It put dashes in three consecutive cells as the result.

=IFS(Western!V5:X5="-","-",Western!V5:X5>=0,"=SUM(Western!V5:X5)")

I feel like I've tried a million combinations of IF. IFS SUMIF, SUMIFS and just can't seem to find the right solution and my brain is become too scrambled to make headway. Also attaching a screenshot of an example using of output where I need the quarterly result to read 0 not ' (dash).

Thanks so very much for any advice you can offer. Kind regards, Lauren

O365 using Windows 10 and Excel desktop version 2102

 

[Zot]

Why not just use cell formatting to show dash when value = 0?

Dash for a zero in Excel

How to display a dash ( - ) instead of a zero in Excel? Follow this step-by-step guide to learn how to customize your numbers.

www.excel-exercise.com

 

[StephenCrump]

Like this?

	A	B	C	D	E
1	0	0	-		0
2	-	-	-		-
3	0	-	0		0
4	1	1	-		2
5	-	2	2		4
Sheet1

Cell Formulas
Range	Formula
E1:E5	E1	=IF(AND(A1:C1="-"),"-",SUM(A1:C1))

I'm assuming that "-" is text, and not a zero value formatted as "-"?

 

[Larry of Oz]

Hi Zot, thank you for the reply.

You'll have to forgive me, I haven't played around with cell formatting that much but the solution I'm after is to show a 0 if the SUM is 0 but if there is a dash in each month, I need the result to show a dash in the output (not a 0). I hope this helps clarify. Am I making sense and was that what your suggestion was meant to achieve?

Many thanks again for taking the time to reply

 

[Larry of Oz]

Stephen that looks exacty like it might work. All your assumptions seem correct. I'll give it a go now and let you know in a few minutes (God willing). Cheers, Lauren

 

[Larry of Oz]

Hey that did the trick Stephen!

Thanks so much for that, I really appreciate you taking the time to respond. Unfortunately I don't get to use formulas as much as I'd like to, so I have to re-figure things out every time. Much appreciation and have a great day

Lauren

 

[StephenCrump]

Great, I'm glad we could help.

 

[Larry of Oz]

Dear all,

With regards to Stephen's solution above, I can't quite get my head around why using the IF with AND function works in this case (output in one cell), while using IF without AND results in output across three cells. I know the question is solved, but it's driving me crazy that I don't understand why it works. If anyone is keen to help me understand it, I'd be most grateful!

With AND (Works)

=IF(AND(Eastern!Z5:AB5="-"),"-",SUM(Eastern!Z5:AB5))

Without AND (Does not work, fills across three cells)

=IF(Eastern!Z5:AB5="-","-","=SUM(Eastern!Z5:AB5")

Thanks in advance

 

[Zot]

Dear all,

With regards to Stephen's solution above, I can't quite get my head around why using the IF with AND function works in this case (output in one cell), while using IF without AND results in output across three cells. I know the question is solved, but it's driving me crazy that I don't understand why it works. If anyone is keen to help me understand it, I'd be most grateful!

With AND (Works)

=IF(AND(Eastern!Z5:AB5="-"),"-",SUM(Eastern!Z5:AB5))

Without AND (Does not work, fills across three cells)

=IF(Eastern!Z5:AB5="-","-","=SUM(Eastern!Z5:AB5")

Thanks in advance

And would mean Z5, AA5, AB5 all must be equal to "-" to be TRUE and only then display "-"

 

[Larry of Oz]

Thanks Zot, by putting AND in the formula, all conditions must be true ZB5:AB5 resulting in a single result, but if using only IF then the result showing across three cells is essenitally saying ZB5=TRUE, AA5=TRUE, AB5=TRUE

Thank you