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

1. 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

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

you could try this...
=IF(COUNTIF(K6:K11,"-")=5,"-",I17)

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

Originally Posted by vill
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

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.

4. 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. 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.

6. 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.

7. 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. 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!

Thanks again!

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

Ok, now I see what you were going for.
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. Re: If ALL cells are "-", then "-", otherwise...

Got it! :-) Thank you

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