Count number of rows matching a criteria

count rows with multiple and criteria
count number of rows with condition in r
r count rows with multiple conditions
count rows in excel with condition
excel count rows with value
countif row and column criteria
count number of observations in r
countif with multiple criteria

I am looking for a command in R which is equivalent of this SQL statement. I want this to be a very simple basic solution without using complex functions OR dplyr type of packages.

Select count(*) as number_of_states 
  from myTable
where  sCode = "CA"

so essentially I would be counting number of rows matching my where condition.

I have imported a csv file into mydata as a data frame.So far I have tried these with no avail.

  1. nrow(mydata$sCode == "CA") ## ==>> returns NULL

  2. sum(mydata[mydata$sCode == 'CA',], na.rm=T) ## ==>> gives Error in FUN(X[[1L]], ...) : only defined on a data frame with all numeric variables

  3. sum(subset(mydata, sCode='CA', select=c(sCode)), na.rm=T) ## ==>> FUN(X[[1L]], ...) : only defined on a data frame with all numeric variables

  4. sum(mydata$sCode == "CA", na.rm=T) ## ==>> returns count of all rows in the entire data set, which is not the correct result.

and some variations of the above samples. Any help would be appreciated! Thanks.

mydata$sCode == "CA" will return a boolean array, with a TRUE value everywhere that the condition is met. To illustrate:

> mydata = data.frame(sCode = c("CA", "CA", "AC"))
> mydata$sCode == "CA"
[1]  TRUE  TRUE FALSE

There are a couple of ways to deal with this:

  1. sum(mydata$sCode == "CA"), as suggested in the comments; because TRUE is interpreted as 1 and FALSE as 0, this should return the numer of TRUE values in your vector.

  2. length(which(mydata$sCode == "CA")); the which() function returns a vector of the indices where the condition is met, the length of which is the count of "CA".

Edit to expand upon what's happening in #2:

> which(mydata$sCode == "CA")
[1] 1 2

which() returns a vector identify each column where the condition is met (in this case, columns 1 and 2 of the dataframe). The length() of this vector is the number of occurences.

How to count how many cells contain certain text or value in Excel?, How do I count the number of rows in Excel with a specific value? To count rows in a table that meet multiple criteria, some of which depends on logical tests that work at the row-level, you can use the SUMPRODUCT function. Context. You have a table that contains the results of sports matches. You have four columns: home team, visiting team, home team score, visiting team score.

sum is used to add elements; nrow is used to count the number of rows in a rectangular array (typically a matrix or data.frame); length is used to count the number of elements in a vector. You need to apply these functions correctly.

All of your solutions show a fundamental misunderstanding if the use of sum and R structures.

Let's assume your data is a data frame named "dat". Correct solutions:

nrow(dat[dat$sCode == "CA",])
length(dat$sCode[dat$sCode == "CA"])
sum(dat$sCode == "CA")

Count the number of rows or columns in Excel, To count rows that contain specific values, you can use an array formula based on the MMULT, TRANSPOSE, Working from the inside out, the logical criteria used in this formula is: The total returned by SUM is a count of all rows that contain the number 90. Excel formula: Count rows with at least n matching values. SQL Count is an inbuilt function in SQL Server. It returns the count of the number of rows matching criteria. Also, it can return the count of all the rows in the table if you don’t specify any criteria. In this article, we will discuss the SQL Count Function. Also, we will discuss a few examples of using it. Syntax

  1. mydata$sCode is a vector, it's why nrow output is NULL.
  2. mydata[mydata$sCode == 'CA',] returns data.frame where sCode == 'CA'. sCode includes character. That's why sum gives you the error.
  3. subset(mydata, sCode='CA', select=c(sCode)), you should use sCode=='CA' instead sCode='CA'. Then subset returns you vector where sCode equals CA, so you should use

    length(subset(na.omit(mydata), sCode='CA', select=c(sCode)))

Or you can try this: sum(na.omit(mydata$sCode) == "CA")

Excel formula: Count rows with multiple OR criteria, But if you have to extract a lot of count of rows for many different criteria, the filter is not the good tool. Use the function COUNTIFS. The COUNTIFS  Count number of rows matching a criteria nrow (mydata$sCode == "CA") ## ==>> returns NULL sum (mydata [mydata$sCode == 'CA',], na.rm=T) ## ==>> gives Error in FUN (X [ [1L]], ) : only defined on a data frame sum (subset (mydata, sCode='CA', select=c (sCode)), na.rm=T) ## ==>> FUN (X [ [1L]],

Just give a try using subset

nrow(subset(data,condition))

Example

nrow(subset(myData,sCode == "CA"))

Excel formula: Count rows that contain specific values, The table consists of 4 columns: “Delivery Number” (column B), “Delivery Date” (​column C), “City” (column D) and “Amount” (column E). In the cell H4, we want to​  If you want to count rows where two (or more) criteria match, you can use a formula based on the COUNTIFS function. In the example shown, we want to count the number of orders with a color of "blue" and a quantity > 15. The formula we have in cell G7 is: =

With dplyr package, Use

 nrow(filter(mydata, sCode == "CA")),

All the solutions provided here gave me same error as multi-sam but that one worked.

COUNTIFS, You can use an operator with a criterion. In this example only the rows where the quantity is  The COUNTIFS function in Excel counts the number of cells in a range that match a set of multiple criteria. COUNTIFS extends the COUNTIF function which only allows one criteria. It is similar to SUMIFS, which will find the sum of all cells that match a set of multiple criteria. COUNTIFS function syntax. The COUNTIFS function has the following syntax:

Excel formula: Count if row meets multiple internal criteria, To count the number of rows where two cirterias are matching the the COUNTIFS function accepts multiple criteria in pairs - each pair  The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets the number of rows or non NULL column values. COUNT() returns 0 if there were no matching rows. Syntax: COUNT(*) COUNT( [ALL|DISTINCT] expression ) The above syntax is the general SQL 2003 ANSI standard syntax.

How to Count in Excel Functions, I believe you are looking for COUNTIFS . This formula must be put in cell C3 and can be copied to the whole table: =COUNTIFS(Sheet2!$B:$B,$A3,Sheet2!$ Hello everyone, I have an issue that I can't figure out. I'm trying to count the numbers of lines in a table where there is no date (actually value is 0 so the date is 01/01/1900) and then I'm displaying that on a graph depending on other columns. My DAX expression is : RestantaFaire = COUNTROWS

Excel Count, In those instances, you might wonder how you could get a count of the number of records that meet more than one criteria. Let's say that you are  Using Dataframe.apply () we can apply a function to all the rows of a dataframe to find out if elements of rows satisfies a condition or not. Based on the result it returns a bool series. By counting the number of True in the returned series we can find out the number of rows in dataframe that satisfies the condition.

Comments
  • sum(mydata$sCode == "CA") should work. We can't help any more without a minimal reproducible example.
  • I would of thought option 4 would work. ex. dat <- data.frame(x=c("a", "b", NA)) ; sum(dat$x=="a", na.rm=T)
  • The use of "NROW" (capital letters) works for a vector, but it can be misleading and should be avoided in proper coding.
  • @SteveKern: are you asking for sample data?
  • @user20650: Could you please clarify? what does x represent in your statement - data.frame(x=c("a", "b", NA)) ; ? I presume c is required per syntax, "a" and "b" are state codes like "CA" and "WA"?
  • @mult-sam, do you mean that the second suggestion I provided (length(which(mydata$sCode == "CA"))) doesn't work for you? Without access to your data I'm not sure why that would be- using the simple example dataframe provided in my answer that code functions properly.
  • yes its not giving me the correct result. the length statement above is retuning the count of all the rows in the DF, not the correct result.
  • @multi-sam you need to provide a reproducible example. The solution here works on example data sets. If it's not working on your data, you need to provide your data and prove this.
  • solution #2 work for me! Thanks @Joe! @multi-sam, you sure that not all the observations in the DF are from California?? :P Thanks for the question BTW.
  • all these are the ones I have already tried, returning count of all the rows in DF, not a correct result.