Forum Discussion
Solved
Countif function
Hi ,
I have a long list of value and i need to get the value for example, to count any number but not including 3,5,7,20,43,87,91? How do i go about to use COUNTIFS or other function in excel to get the result.? Which i mean i want the total number that is not 3,5,7,20,43,87,91.
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?
10 Replies
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?
