We help IT Professionals succeed at work.

Excel Formula NOT logic

I use the following =COUNTIFS(Raw_20180918!V:V,"approved") to find how many entries are approved?

How do I determine how many entries are NOT approved?
Comment
Watch Question

NorieAnalyst Assistant

Commented:
Matt

Try this.

=COUNTIFS(Raw_20180918!V:V,"<>approved",Raw_20180918!V:V, "<>")
Matt PinkstonEnterprise Architect

Author

Commented:
I am getting 1048576 like it is reading a ton a blank lines at the end?
Matt PinkstonEnterprise Architect

Author

Commented:
I am getting 1048576 like it is reading a ton a blank lines at the end?
Rob HensonFinance Analyst

Commented:
Try:
=COUNTA(Raw_20180918!V:V)-COUNTIFS(Raw_20180918!V:V,"approved")

This counts all non-blank cells and deducts those with "Approved"
Finance Analyst
Commented:
Just thought, if the only other option to "Approved" is blank then COUNTA on column V isn't going to work.

I assume the Approved is against a reference or description of some sort in another column, lets assume column U and then you can use:

=COUNTA(Raw_20180918!U:U)-COUNTIFS(Raw_20180918!V:V,"Approved")
NorieAnalyst Assistant

Commented:
The 2nd criteria in the formula I posted should exclude blank cells from the count

If it isn't then there might be a problem with the data, so it might be better to use the formula Rob suggested.
Matt PinkstonEnterprise Architect

Author

Commented:
thanks