Formula to display dash vs zero based on cells

Larry of Oz

New Member
Joined
Jun 2, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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
    Locations.JPG
    56.9 KB · Views: 8
  • Quarterly Summary.JPG
    Quarterly Summary.JPG
    23.2 KB · Views: 8

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,998
Office Version
  1. 2016
Platform
  1. Windows
Why not just use cell formatting to show dash when value = 0?

 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,398
Office Version
  1. 365
Platform
  1. Windows
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 "-"?
 
Solution

Larry of Oz

New Member
Joined
Jun 2, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jun 2, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 2, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Sep 18, 2013
Messages
4,398
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Great, I'm glad we could help.
 

Larry of Oz

New Member
Joined
Jun 2, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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 :)
 

Attachments

  • Results.JPG
    Results.JPG
    12.1 KB · Views: 4

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,998
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jun 2, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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 :)
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
1,151,700
Messages
5,766,007
Members
425,322
Latest member
galaxy6623top

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top