Forum Discussion
Countif function
Note: columns are vertical, rows are horizontal. It is confusing if you label a column as a "row A".
You can count the number of cells that don't have the value "aa" like this:
=COUNTIF(B2:B20,"<>aa")
And to sum the values of column C where column A is not "aa" you can use
=SUMIF(B2:B20,"<>aa",C2:C20)
If you want to use whole column references instead of row 2 to row 20, that can be done, too, but for the Countif() you will need to subtract 1 for the header (wrongly) named Row A.
Does that do what you need?
Hello,
use a formula along the lines of
=COUNTIFS(A:A,">0",A:A,"<>2",A:A,"<>4")
For each number you want to include, add a condition pair like
A:A,"<>4"
If your Excel uses semicolons in formulas, please use semicolons instead of commas.
Hi,
Thank you. For A:A,">0(anything that is bigger than or smaller than 0 , A:A,"<>2, (anything that is bigger than 2 and smaller than 2). By doing this will it include 0 as well? i did try the formula and manually count it. i did get the result. Can i say, it does understand the requirement i.e to say, if i need anything lesser than 2 or bigger than 2, lesser than 0 but bigger than ), it will not include 0 in it since it is in the formula? am i right to say that.
=COUNTIFS(A:A,">0",A:A,"<>2",A:A,"<>4")
Just to clarify: the formula I posted above - '<>' is the "not equal" operator, so the formula
=COUNTIFS(A:A,">0",A:A,"<>2",A:A,"<>4")
will count everything that is
>0 -- greater than zero AND at the same time
<>2 -- not equal to 2 AND at the same time
<>4 -- not equal to 4
Does that make it clearer?
Hi,
Thanks, that definitely helps alot. i understand the BOOLEAN concept and thanks as well for the explaination. i did tried out the formula and i think it did not include the number that is in the formula.=COUNTIFS(A:A,">0",A:A,"<>2",A:A,"<>4")
>0 -- greater than zero AND at the same time
<>2 -- not equal to 2 AND at the same time (0 is not included in this since the criteria and formula has 0 in it)
<>4 -- not equal to 4 ( 2 and 4 not included as they are part of the formula as well. )
And what if the requirement was this
AA - 1 , BB - 1 , CC - 2 , DD - 1 , AA - 1 , BB - 1 , AA - 1 , DD - 1 , AA - 1 .
and i require to count how many not AA are there? those are short list. i have couple of hundreds of AA to ZZ and what we did now was to filter it one at a time and calculate it.
Countifs() uses a boolean AND to combine the conditions, so ALL conditions must be TRUE for the row to be counted. You need to work out the logic step by step. You can use
"<>0"
as one condition to exclude zero values. I'm not quite sure if you want to include values smaller than zero. Can you work with this or do you need further help?