FAQ Database Discussion Community


Count if every nth range with a single criteria

excel-formula,countif
I have 1000 rows and have a single column, single criteria. I want to do countif for this data for a set of 50 rows at one time. How do I update the row number automatically so that I don't need to enter the range manually everytime? Additional information (from...

IF duplicate cell value found in column then return value

excel,if-statement,indexing,match,countif
I need to track a person in a data sheet to determine from which location to which location the person moved. If a person appears more then one time in Column J that means the person has changed the location and the location value is in Column L. For this...

Static Formulas in Excel

excel,static,formula,countif
I have a formula in excel that looks like such: =COUNTIF(Imports!$B$2:$B$999,"1") It works like I want it to but part of my manipulation of the spreadsheet requires me to insert new columns in the sheet that formula is referencing. When I do that it changes the formula from column B...

Counting across rows for ###, then countif down columns for ids matching ###

excel,excel-formula,countif
I'm trying to count the number of activities each organization has done in my dataset. Right now, each row represents a single organization's list of activities. The following formula accurately finds the number of activities per organization: =IF(COUNTA(A1:H1)=5,"yes") However, I now need to group organizations by amount of activities (ex:...

Count cells in column C, excluding instances where Column E contains specific text

excel,google-spreadsheet,countif
I would like to count the number of occasions a report has been logged (column c) in the last 50 days, excluding cases where reports have been marked as "Test" in column E. I have a working formula for the first half, but I can't seem to get a figure...

Count frequencies if values of other column are equal to specific text

excel,excel-formula,worksheet-function,countif
City Job London Webdesigner London Webdesigner Tokio Lawyer Tokio Teacher Buenos Aires Manager London Athlet Rome Doctor London Webdesigner I'm looking for a way to count the frequency of Webdesigner if "City" is equal to London (in same row). The result of the formula should be 3....

Count based on column and row

arrays,excel,countif
I can't seem to find something quite like this problem... I have an array table where each row contains a random assortment of numbers 1-N On another sheet, I have a table with column and row headers numbered 1-N I want to count how many rows in the array contain...

countif and sumif each row without grouping

sql,sum,countif
I'm trying to figure out sumif and countif using SQL Here is the code to start with: SELECT ItemID , Supplier , Cost , SalePrice FROM countifExpirement I'm trying to create this output: First four columns already have data and i'm trying to create calculated fields to calculate duplicate Items,...

COUNTIF with OR Statement

excel,excel-formula,worksheet-function,excel-2013,countif
I'm trying to count the number of times two statements occur. The problem is that if the first statement occurs then it won't check for the second. Maybe if easier if I explain like this: If statement1= true then count = 1 and move next else if statement2= true count...

If the row contains specific text highlight row by row where two columns are the same

excel,excel-formula,worksheet-function,conditional-formatting,countif
I currently use conditional formatting on columns M and N on a spreadsheet to fill duplicate number values using the formula: =COUNTIF($M1:$N1,M1)>1 I was asked by my boss if I could only conditionally format columns M and N if a specific text name in column G appears on that same...

How to perform countif in R and export the data?

r,export,countif
I have a set of data: ID<-c(111,111,222,222,222,222,222,222) TreatmentDate<-as.Date(c("2010-12-12","2011-12-01","2009-8-7","2010-5-7","2011-3-7","2011-8-5","2013-8-27","2016-9-3")) Treatment<-c("AA","BB","CC","DD","AA","BB","BB","CC") df<-data.frame(ID,TreatmentDate,Treatment) df ID TreatmentDate Treatment 111 12/12/2010 AA 111 01/12/2011 BB 222 07/08/2009 CC 222 07/05/2010 DD 222 07/03/2011 AA 222 05/08/2011 BB 222 27/08/2013 BB 222 03/09/2016 CC I also have another dataframe showing the test date for each subject:...

Excel complicated count function

excel,count,countif
I have kind of a complicated count I can't seem to figure out. Basically I have a table that looks similar to this image: Now what I need to do is: Tally the number of 1's if the category = "Anon" in one cell, then count the number of 2's...

Count how often a value in a cell in one specific column is not the same as the value in the same row of a specific other column

excel,sum,excel-formula,worksheet-function,countif
Both ColumnA and ColumnB are blank except for O and R (individually). How can I count the number of times a row contains O in ColumnA and R in the same row of ColumnB together with a row that contains R in ColumnA and O in the same row of...

COUNTIF combined with OR

excel,excel-formula,worksheet-function,countif
I want a formula for count how many True/Yes/Si are in the range. If A1 is not empty i want to count. I don't know how to put the 3 parameters in the formula =IF(A1="";"";COUNTIF(A2:A5;"TRUE")) <- I need to add Yes and Si. Edit: I forgot to say that I...

IF statement to show if ALL cells in a row have the same value

excel,excel-formula,worksheet-function,countif
I want to write a formula in Excel to determine if EVERY cell in a row has the same value. There are five cells, for example: C2:G2 that I need to filter out only the ones with the same value, so if only one out of the five cells has...

adding up numbers after an if operation in R

r,countif
I have a set of data which consists of the number of courses of medication that a patient has taken at a certain date. subject<-c(111,111,111,222,222,333,333,333,333) date<-as.Date(c("2010-12-12","2011-12-01","2009-8-7","2010-5-7","2011-3-7","2011-8-5","2013-8-27","2016-9-3","2011-8-5")) medicationCourses<-c(1,0,NA,3,4,2,4,5,6) data<-data.frame(subject,date,medicationCourses) data subject date medicationCourses 1 111 2010-12-12 1 2 111 2011-12-01 0 3 111 2009-08-07 NA 4 222 2010-05-07 3 5 222...

Differences between two columns [closed]

excel,if-statement,excel-formula,worksheet-function,countif
I have such Excel data: I want to find records in ColumnA which do not exist in ColumnB....

Aging formula without using additional column

excel-formula,countif
I have a table like below ID StarDate EndDate 123 6/5/2015 15:54 6/12/2015 15:54 124 6/6/2015 15:54 6/8/2015 15:54 125 6/7/2015 15:54 6/9/2015 15:54 126 6/8/2015 15:54 6/11/2015 15:54 127 6/9/2015 15:54 6/15/2015 15:54 I want to get the total count of aging without using any additional column 0-30 =COUNTIFS(A:A,">124",A:A,"<127",TODAY()-B2:B6,"<=30")...

VLOOKUP query help required - count from a range

excel,excel-formula,vlookup,worksheet-function,countif
I'm trying to build a spreadsheet to help automate points scoring for an office F1 fantasy league we have. I've attached an example data set, but basically I need to search a range, then count how many times the constructor appears in the numbered positions (discounting R, D as they've...

Identify all instances of a set where any one instance of the set is paired with a specific value

excel,excel-formula,worksheet-function,conditional-formatting,countif
I am trying to highlight data by row based on an a ref number. The system I pull the report from creates a new event and thus new row item for each action. So every time it breaches or is achieved a new line is created and I am only...

VBA Why does Application.Countif return an array or error 424

arrays,excel,vba,excel-vba,countif
I would like to count the number of matching items in an array. I tried using Application.Countif MyCount = Application.WorksheetFunction.CountIf(Myrange, val) but this returns an array full of errors rather than a simple count. I have also tried using Application.WorksheetFunction.Countif but this causes a 424 error. I currently am testing...

Conditional count

excel,excel-formula,worksheet-function,countif
I want functions which will: add +1 to cell I3 for every W, L, T, IP and R in cells from F8 to F10, so now it has to be 3 because cells F8, F9 and F10 are populated: If F11 were populated also, then it has to be 4....

Dynamic formula with quotes inside a COUNTIFS function

excel,excel-formula,excel-2013,countif,excel-indirect
I am trying to adapt the following formula (counting instances of values between 0 and 24): =COUNTIFS(cancellations!AG2:AG408,">0",cancellations!AG2:AG408,"<24") (formula evaluates to 75), to something like: =COUNTIFS(cancellations!AG2:AG408,">0",cancellations!AG2:AG408,INDIRECT("" & "<" & B1*24 & "")) but this evaluates to zero. (B1 = 1 in the above example.) When I view the INDIRECT function inside...

Counting values embedded in strings inside a column (Google Spreadsheets)

excel,google-spreadsheet,excel-formula,formula,countif
I have a Google Survey where I created some multiple choice questions. Now, I am trying to count the responses. [A] [B] [Response#] [Selections] [1] [Apple,Orange,Banana] [2] [Orange,Banana] [3] [Apple,Orange,Banana] [4] [Banana] [5] [Apple,Banana] [6] [Apple,Orange] . So on my summary spreadsheet, I would like to have the totals: [Favorite...

Combined COUNTIF for several columns

excel,excel-formula,worksheet-function,countif
We have a table with values like this: I need a formula for cell D1 to count rows that have in column A value "one" and column B is not empty. For example: in first row A1==one but B1 is empty; (that doesn't count) in second row A2==one and B2...