Count of cells in a certain column the does not contain a specified value

Question!

I can't find anywhere a function that would allow me to count every cell in a column except a certain value given.

For example there is a column where the values are (-), 1, 2 and 3 , I want to count all the cells that are not "(-)".

So there should be a function like

=countif(A1:A100, Not "(-)")

Does anyone how to do that?



Answers

This can be done using <> for not equal, like so:

=COUNTIF(A1:A100, "<>-")

Replace the - with what you want to not count, and change A1:A100 to the range of data you would like the formula to consider.



This can be done, please see below for a very simple (and rough - rotation does not work properly!) example, which allows you to update the size of the table view by entering a number in the text field and resetting with a button.

import UIKit

class ViewController: UIViewController {

    var tableViewController : FlexibleTableViewController!
    var textView : UITextView!
    var button : UIButton!
    var count : Int! {
        didSet {
            self.refreshDataSource()
        }
    }
    var dataSource : [Int]!
    let rowHeight : CGFloat = 50

    override func viewDidLoad() {
        super.viewDidLoad()

        // Configure

        self.tableViewController = FlexibleTableViewController(style: UITableViewStyle.plain)

        self.count = 10
        self.tableViewController.tableView.backgroundColor = UIColor.red

        self.textView = UITextView()
        self.textView.textAlignment = NSTextAlignment.center
        self.textView.textColor = UIColor.white
        self.textView.backgroundColor = UIColor.blue

        self.button = UIButton()
        self.button.setTitle("Reset", for: UIControlState.normal)
        self.button.setTitleColor(UIColor.white, for: UIControlState.normal)
        self.button.backgroundColor = UIColor.red
        self.button.addTarget(self, action: #selector(self.updateTable), for: UIControlEvents.touchUpInside)

        self.layoutFrames()

        // Assemble
        self.view.addSubview(self.tableViewController.tableView)
        self.view.addSubview(self.textView)
        self.view.addSubview(self.button)
    }

    override func didReceiveMemoryWarning() {
        super.didReceiveMemoryWarning()
        // Dispose of any resources that can be recreated.
    }

    func refreshDataSource() -> Void {
        if let _ = self.dataSource {
            if !self.dataSource.isEmpty {
                self.dataSource.removeAll()
            }
        }
        else
        {
            self.dataSource = [Int]()
        }

        for count in 0..<self.count {
            self.dataSource.append(count)
        }

        self.tableViewController.dataSource = self.dataSource
        self.tableViewController.tableView.reloadData()
        if let _ = self.view {
            self.layoutFrames()
            self.view.setNeedsDisplay()
        }
    }

    func updateTable() -> Void {
        guard let _ = self.textView.text else { return }
        guard let validNumber = Int(self.textView.text!) else { return }

        self.count = validNumber
    }

    func layoutFrames() -> Void {

        if self.tableViewController.tableView != nil {
            self.tableViewController.tableView.frame = CGRect(origin: CGPoint(x: self.view.frame.width / 2 - 100, y: 100), size: CGSize(width: 200, height: CGFloat(self.dataSource.count) * self.rowHeight))
            NSLog("\(self.tableViewController.tableView.frame)")
        }

        if self.textView != nil {
            self.textView.frame = CGRect(origin: CGPoint(x: 50, y: 100), size: CGSize(width: 100, height: 100))
        }

        if self.button != nil {
            self.button.frame = CGRect(origin: CGPoint(x: 50, y: 150), size: CGSize(width: 100, height: 100))
        }
    }
}

class FlexibleTableViewController : UITableViewController {

    var dataSource : [Int]!

    override init(style: UITableViewStyle) {
        super.init(style: style)
    }

    required init?(coder aDecoder: NSCoder) {
        fatalError("init(coder:) has not been implemented")
    }

    override func numberOfSections(in tableView: UITableView) -> Int {
        return 1
    }

    override func tableView(_ tableView: UITableView, numberOfRowsInSection section: Int) -> Int {
        return self.dataSource.count
    }

    override func tableView(_ tableView: UITableView, cellForRowAt indexPath: IndexPath) -> UITableViewCell {
        self.tableView.register(UITableViewCell.self, forCellReuseIdentifier: "cell")

        let cell = tableView.dequeueReusableCell(withIdentifier: "cell") ?? UITableViewCell()

        cell.frame = CGRect(origin: CGPoint(x: 10, y: 5), size: CGSize(width: 180, height : 40))
        cell.backgroundColor = UIColor.green

        return cell
    }

}

Whether it is a good idea or not, is, as has been pointed out, another question! Hope that helps!

By : Sparky


The majority of your concerns seem to boil down to either misuse or misunderstanding.

  • much bigger codesize

    This is usually a result of properly respecting both the Single Responsibility Principle and the Interface Segregation Principle. Is it drastically bigger? I suspect not as large as you claim. However, what it is doing is most likely boiling down classes to specific functionality, rather than having "catch-all" classes that do anything and everything. In most cases this is a sign of healthy separation of concerns, not an issue.

  • ravioli-code instead of spaghetti-code

    Once again, this is most likely causing you to think in stacks instead of hard-to-see dependencies. I think this is a great benefit since it leads to proper abstraction and encapsulation.

  • slower performance Just use a fast container. My favorites are SimpleInjector and LightInject.

  • need to initialize all dependencies in constructor even if the method I want to call has only one dependency

    Once again, this is a sign that you are violating the Single Responsibility Principle. This is a good thing because it is forcing you to logically think through your architecture rather than adding willy-nilly.

  • harder to understand when no IDE is used some errors are pushed to run-time

    If you are STILL not using an IDE, shame on you. There's no good argument for it with modern machines. In addition, some containers (SimpleInjector) will validate on first run if you so choose. You can easily detect this with a simple unit test.

  • adding additional dependency (DI framework itself)

    You have to pick and choose your battles. If the cost of learning a new framework is less than the cost of maintaining spaghetti code (and I suspect it will be), then the cost is justified.

  • new staff have to learn DI first in order to work with it

    If we shy away from new patterns, we never grow. I think of this as an opportunity to enrich and grow your team, not a way to hurt them. In addition, the tradeoff is learning the spaghetti code which might be far more difficult than picking up an industry-wide pattern.

  • a lot of boilerplate code which is bad for creative people (for example copy instances from constructor to properties...)

    This is plain wrong. Mandatory dependencies should always be passed in via the constructor. Only optional dependencies should be set via properties, and that should only be done in very specific circumstances since oftentimes it is violating the Single Responsibility Principle.

  • We do not test the entire codebase, but only certain methods and use real database. So, should Dependency Injection be avoided when no mocking is required for testing?

    I think this might be the biggest misconception of all. Dependency Injection isn't JUST for making testing easier. It is so you can glance at the signature of a class constructor and IMMEDIATELY know what is required to make that class tick. This is impossible with static classes since classes can call both up and down the stack whenever they like without rhyme or reason. Your goal should be to add consistency, clarity, and distinction to your code. This is the single biggest reason to use DI and it is why I highly recommend you revisit it.

By : David L


This video can help you solving your question :)
By: admin