Forum Discussion
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?
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")
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?
