# Formula to display dash vs zero based on cells

#### Larry of Oz

##### New Member
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

#### Attachments

• Locations.JPG
56.9 KB · Views: 8
• Quarterly Summary.JPG
23.2 KB · Views: 8

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### Zot

##### Well-known Member
Why not just use cell formatting to show dash when value = 0?

#### StephenCrump

##### MrExcel MVP
Like this?

ABCDE
100-0
2----
30-00
411-2
5-224
Sheet1
Cell Formulas
RangeFormula
E1:E5E1=IF(AND(A1:C1="-"),"-",SUM(A1:C1))

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

#### Larry of Oz

##### New Member
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

##### New Member

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

##### New Member
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

##### MrExcel MVP

Great, I'm glad we could help.

#### Larry of Oz

##### New Member
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")

#### Attachments

• Results.JPG
12.1 KB · Views: 4

#### Zot

##### Well-known Member
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")

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

#### Larry of Oz

##### New Member
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

Replies
1
Views
1K
Replies
3
Views
124
Replies
0
Views
222
Replies
16
Views
627
Replies
14
Views
2K

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,697
Messages
5,765,982
Members
425,320
Latest member
Galin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back