+ Reply to Thread
Results 1 to 10 of 10

If ALL cells are "-", then "-", otherwise...

  1. #1
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Windows 10
    Posts
    270

    If ALL cells are "-", then "-", otherwise...

    Hi guys, is there a simple formula to this, would you please suggest? Not able to attach neither a file, or insert an image in here, so here is screenshot, showing the same:

    https://www.screencast.com/t/AcOSwMgl

    Thanking in advance!

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,865

    Re: If ALL cells are "-", then "-", otherwise...

    you could try this...
    =IF(COUNTIF(K6:K11,"-")=5,"-",I17)
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: If ALL cells are "-", then "-", otherwise...

    Quote Originally Posted by vill View Post
    Hi guys, is there a simple formula to this, would you please suggest? Not able to attach neither a file, or insert an image in here, so here is screenshot, showing the same:

    https://www.screencast.com/t/AcOSwMgl

    Thanking in advance!
    Re posting files / images

    Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Windows 10
    Posts
    270

    Re: If ALL cells are "-", then "-", otherwise...

    Thanks Sambo kid, it works! Just a question: what does 5 stand for, and what part does it play in a formula? Obviously, more cells will be added in between, so hoping is not relative to cell number in a draft?

    @Special-K: I had Database Error, looked like whole ExcelForum site was down, at least on my end. But should work now, I guess. Thanks!

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,865

    Re: If ALL cells are "-", then "-", otherwise...

    in the file you linked it noted that the ALL the cells of K6:K11 (which is 6) had to have "-" or otherwise display the value in I17. So the countif is looking at the range of K6:K11 and counting how many "-" there are and IF that number equals 6 then return "-", otherwise return the value in I17.
    Sorry, the actual formula should be =IF(COUNTIF(K6:K11,"-")=6,"-",I17) not 5, I miscounted.
    But if you are going to have a "floating" number then the formula might need to be adjusted to something that would handle a different number of "-" and those that might contain a number or other symbol. So to have an adjustment done you might have to provide a more detailed explanation about what could change and what the expected results would be.
    Last edited by Sam Capricci; 06-20-2018 at 10:11 AM.

  6. #6
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Windows 10
    Posts
    270

    Re: If ALL cells are "-", then "-", otherwise...

    Thank you Sambo kid, that explains. And yep, I can see how playing with number 5 or 6 can get complicated...

    Since I could not attach a file last time, doing it here, now:

    https://www.excelforum.com/attachmen...1&d=1529502055

    So you see, there are not much more options to be used there - there will need to be cells added in and out between K6:K11 (obviously) But as soon as anything else apart from "-" appears in any of the cells in between K6:K11, it would mean that some balances got added. and a month is now active.

    And all what needs to be done is just like per posting - if all cells in K6:K11 are "-", means that no balances are yet added, hence, a months is inactive and K5 should say "-". As soon as even a single balance is added in K6:K11, K5 should display balance from I17.

    But once again, baring in mind that there will be more cells added in and out between K6:K11.

    Thanks for looking into this, appreciate it.
    Attached Files Attached Files

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,865

    Re: If ALL cells are "-", then "-", otherwise...

    I'm not sure I'm seeing the difference between what you wrote in the first post and the sixth post. So my recommendation for the formula in K5 is =IF(COUNTIF(K6:K11,"-")=6,"-",I17) if you want ALL the cells from K6 to K11 to have the "-" and then bring back the value in I17.

    OR, are you saying that you want it to sum the values in K6 through K11 instead of pointing it to I17? Then I would change it to this...
    =IF(COUNTIF(K6:K11,"-")=6,"-",SUM(K6:K11))

    BUT, either of these will work if you want - to appear in K5 if ALL the cells from K6 to K11 contain "-" or if any of those cells contain a number then what will appear in K5 is that number if it appears in I17 (first formula) or you want it to be the sum of K6:K11 (second formula).

    NOW, all that being said, your regional settings may require you to replace the commas with semicolons (, to if the formulas don't work for you.

    Otherwise you'll have to help me to better understand if I'm missing the point.

  8. #8
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Windows 10
    Posts
    270

    Re: If ALL cells are "-", then "-", otherwise...

    I've been scratching my head how else to share... and idea popped in! I updated formula to:

    =IF(COUNTIF(K6:K11,"-")=ROWS(K6:K11),"-",I17)

    So it basically instead of me just stating number 6, it now counts all rows in between K6:K11. And that was a challenge with initial formula (I would not be updating number 6 every time rows are added in / removed)

    Anyhow, it works now. Thank you sir, a million!

    Already left reputation, closing a Thread now :-)

    Thanks again!

  9. #9
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,865

    Re: If ALL cells are "-", then "-", otherwise...

    Ok, now I see what you were going for.
    glad you worked it out.
    AND thank you for the rep!
    and BTW, my wink in post #7 was supposed to be a semicolon ; but the parens was too close.

  10. #10
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Windows 10
    Posts
    270

    Re: If ALL cells are "-", then "-", otherwise...

    Got it! :-) Thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 02-05-2019, 12:03 AM
  2. Replies: 2
    Last Post: 07-24-2017, 02:19 AM
  3. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  4. Replies: 5
    Last Post: 01-23-2014, 11:02 AM
  5. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  6. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  7. [SOLVED] Data validation: allow entry into a cell if other three cells have "X", "Y" and "Z"?
    By RogerRangeRover in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-05-2013, 04:49 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1