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?
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.
so, are these all in one cell, or in one row or in one column? It's hard to give you a formula without seeing how the data is structured. Could you make a sample file, manually type in the desired results and upload that file here?