One option would be to merge the two datasets by subject/hospitalSubject in the two datasets, subset the rows with date <= admissionDate, and get the sum of 'medicationCourses' grouped by 'subject/admissionDate' with aggregate d1 <- subset(merge(data, hospitalData, by.x='subject', by.y='hospitalSubject'), date <= admissionDate) aggregate(medicationCourses~subject+admissionDate, d1, sum, na.rm=TRUE, na.action=NULL) # subject admissionDate...

Interesting question. In general, one can use the fact that R evaluates TRUE as 1 and FALSE as 0 to do a lot of COUNTIF-type work. In this case, though, you want it along the vector. Writing a loop would certainly work, but this is R, so we need to...

Sub Copy_Sum() Dim ws As Worksheet 'Selecting the worksheets to loop through K = 1 For Each ws In ThisWorkbook.Worksheets 'Skiping the sheet1 If ws.Name <> "Sheet1" Then 'Counting the number of rows for automation rowsCount = Cells(Rows.Count, 1).End(xlUp).Row temp = 0 'Looping throught the cells for the calculation For...

So if I understand correctly, you want to put a formula in one cell that looks at the range B12:B21 and: If any cell in that range has "No Go" you want it to return "No Go". If none of them are "No Go" then check to see if any...

Your are right that since your vector is const, your iterators are also going to be const. But consequently, the parameter of the predicate function must also be const. You need: const std::string &s...

excel,if-statement,indexing,match,countif

Here is a User Defined Function (aka UDF) to accomplish the task. Function my_Travels(nm As Range, loc As Range, cal As Range) Dim n As Long, cnt As Long, v As Long, vLOCs As Variant, vTMPs As Variant Dim iLOC As Long, sTMP As String my_Travels = vbNullString '"no travels"...

assuming that you start from cell A1, =SUMPRODUCT((list name to search for)*(value to search in the data area)) =SUMPRODUCT(($B$2:$B$6="Anon")*($C$2:$I$6=1)) =SUMPRODUCT(($B$2:$B$6="Anon")*($C$2:$I$6=2)) ... =SUMPRODUCT(($B$2:$B$6="Watterson")*($C$2:$I$6=1)) ...etc....

excel,excel-formula,worksheet-function,countif

Please try: =COUNTIF(C2:G2,C2)=5 ...

I venture to bet on this guess: You are not using an English Excel. Your Excel version uses , as decimal mark. The conversion of yel to a string leaves it with a , but the internal engine only understands the English .. Try if this works: Dim strYel As...

excel,excel-formula,worksheet-function,conditional-formatting,countif

The way I understand your requirement, please select N11:O11 and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format, Format values where this formula is true:, enter: =COUNTIF($B11:$B20,"other")>0 Format..., choose your formatting, OK, OK....

I found the answer, you can do it using the countifs formula. =COUNTIFS(D2:D101;1;AG2:AG101;1) =COUNTIFS(D2:D101;2;AG2:AG101;1) =COUNTIFS(D2:D101;3;AG2:AG101;1) =COUNTIFS(D2:D101;4;AG2:AG101;1) =COUNTIFS(D2:D101;1;AG2:AG101;2) =COUNTIFS(D2:D101;2;AG2:AG101;2) =COUNTIFS(D2:D101;3;AG2:AG101;2) =COUNTIFS(D2:D101;4;AG2:AG101;2) ...

You can use SUMPRODUCT together with FIND to do this. =SUMPRODUCT(--NOT(ISERROR(FIND("G",$A$1:$A$10)))) This basically counts for how many values in A1:A10 the FIND function does not return an error....

Use INDIRECT to reference range from its text address: =COUNTIF(INDIRECT("Imports!$B$2:$B$999"),"1") ...

By my reading, it seems as if you may be trying to do both tasks in a single formula. This may be possible with a more complex formula, but a straightforward solution is to simply add a column with activity counts to the raw data, and then count the instances...

excel,excel-2007,excel-formula,constants,countif

You can use SUMPRODUCT like this: =SUMPRODUCT(1*(name=G6)) ...

This SUMPRODUCT formula will do it: =SUMPRODUCT((C2:D43="filler")*(MOD(ROW(C2:D43),2)=0)) ...

excel,google-spreadsheet,excel-formula,formula,countif

You would have to use wildcards.. please try: =countif('Responses'!B:B,"*"&A2&"*") and see if that works ?...

excel,if-statement,excel-formula,worksheet-function,countif

With COUNTIF: =IF(COUNTIF(A:A,a2)>15,1,IF(COUNTIF(A:A,a2)>10,2,IF(COUNTIF(A:A,a2)>5,3,IF(COUNTIF(A:A,a2)>=1,4,5)))) ...

I don't agree with your results corresponding to 4/2, which surely should be 3, not 2, but this formula, based on the array table being in Sheet1 A1:D4 and the results table being in Sheet2 A1:F6, placed in cell B2 of the latter, should work: =IF($A2=B$1,"-",SUMPRODUCT(N(MMULT(N(COUNTIF(OFFSET(Sheet1!$A$1:$D$1,ROW(Sheet1!$A$1:$D$4)-MIN(ROW(Sheet1!$A$1:$D$4)),),CHOOSE({1,2},B$1,$A2))>0),{1;1})=2))) Copy across and down...

excel,excel-formula,worksheet-function,conditional-formatting,countif

If what you want is all instances of a specific INCnnn to be identified where any one of the specific INCnnn is in the same row as "BREACHED" then Conditional Formatting would do this, say as below assuming your data starts with 1 in A1 and is in three columns....

excel,google-spreadsheet,countif

This would seem to be pretty cross-compatible between Google-Docs and Excel. =countifs(E:E, "<>*TEST*", C:C, ">"&(today()-50)) Not sure from your narrative whether that should be ..., ">="&(today()-50)). Check the results closely to make sure....

You could use SUMPRODUCT: =SUMPRODUCT(($A$2:$A$10="NC")*($D$2:$H$10="a")) for example. Note this counts both "a" and "A"....

This can be done by first reshaping and then summarising. Here is an example using reshape2 and dplyr (data is dat). ## First reshape the data library(reshape2) m <- melt(dat, id.vars=c("Start.Date", "End.Date"), value.name = "Area", variable.name="Area.Pos") ## Summarise, grouping by Area library(dplyr) m %>% group_by(Area) %>% summarise(Start.Occurences = sum(Area.Pos ==...

excel-formula,excel-2010,frequency,excel-2013,countif

no need to formula, select the columns in which you are looking for the duplicates then go to your DATA tab in excel, and click on remove duplicates in the data tools section. There you go! ...

excel,excel-formula,worksheet-function,conditional-formatting,countif

The ties may be broken with a COUNTIF adjustment. For example a CF rule formula of the kind: =RANK(A1,$A$1:$O$1,0)+COUNTIF($A$1:A$1,A1)-1<7 <7 for the six you specified, though it depends upon when ranking ascending or descending. Ties are broken "in order" - the first found is accepted (count of 1 less 1...

We could join the two dataset with 'ID', create a column that checks the condition ('indx'), and use dcast to convert from 'long' to 'wide' format library(data.table)#v1.9.5+ dcast(setkey(setDT(df), ID)[SubjectTestDate][, indx:=sum(TreatmentDate <=Testdate) , list(ID, Treatment)], ID+Testdate~ paste0('Treatment', Treatment), value.var='indx', length) # ID Testdate TreatmentAA TreatmentBB TreatmentCC TreatmentDD #1: 111 2012-12-31 1...

excel,excel-formula,worksheet-function,countif

Assuming City is in A1 please try: =COUNTIFS(A:A,"London",B:B,"Webdesigner") ...

excel,if-statement,excel-formula,worksheet-function,countif

So after struggling to understand what was going on, here is the simple solution: first let me clarify again: column A: but pasted as text originally and in hhmm format, but you need to reformat them as simple numbers, so you will lose those 0 s at the beginning of...

excel,excel-formula,vlookup,worksheet-function,countif

You need to be consistent with spellings or this won't work, but for standings B2 you might try: =IF(COUNTIFS(race1!C:C,A2,race1!A:A,"<>D",race1!A:A,"<>R")=2,20,IF(COUNTIFS(race1!C:C,A2,race1!A:A,"<>D",race1!A:A,"<>R")=1,7,-15)) and in C2: =COUNTIF(race1!$C$2:$C$11,A2)*10 both copied down to suit. Edit for number of cars in column B change first formula to: =COUNTIFS(race1!C:C,A2,race1!A:A,"<>D",race1!A:A,"<>R") and for points put first formula in C2 (copy...

This formula can help: =COUNTIFS(K:K,"abc",J:J,">="&J2,J:J,"<="&J15) Where K:K is the range where you have the usernames and "abc" is a sample name. And J:J is the timestamps range. J2 contains begin date and J15 contains end date for the required range. Make sure date in J2 is lower than the date...

In Standard SQL you would use analytic functions for this. They are not available in every DBMS, however. select itemid, supplier, cost, saleprice, count(*) over (partition by itemid) as count_itemid, count(*) over (partition by supplier) as count_supplier, sum(cost) over (partition by supplier) as cost_suppler from countifexpirement; Here is the same...

Yes, SUMPRODUCT is an option - say you want every 5th cell starting at A2 (i.e. A2, A7, A12 etc.) you can use this formula to count instances of "text" in those cells (possibly within other text) =SUMPRODUCT((MOD(ROW(A2:A1000)-ROW(A2),5)=0)+0,ISNUMBER(SEARCH("text",A2:A1000))+0) change the 5 to whatever n you want - it always starts...

you can use this one: =COUNTIF((INDIRECT("A"&(ROW()-1)*50+1&":A"&(ROW()-1)*50+50)),">="&18) fill down 20 cells; then you will have countifof the every 50th range....

excel,excel-formula,worksheet-function,countif

Please try: =COUNTIFS(A:A,"one",B:B,"<>") ...

As I mentioned in comments, it seems that range G7:G13 contains "yes"/"no" with additional leading/trailing spaces. Try next formula: =SUMPRODUCT(1*(TRIM(G7:G13)="YES")) ...

If your table starts in cell A1, with the first start date in cell B2, try =SUMPRODUCT(--(C2:C6-B2:B6<=30)) Edit after clarification: =SUMPRODUCT(--(A2:A6>124),--(A2:A6<127),--(TODAY()-B2:B6<=30)) ...

excel,if-statement,excel-formula,worksheet-function,countif

Assuming your data starts in Row1 please try: =IFERROR(MATCH(A1,B:B,0),"#") in Row1 and copy down to suit. # indicates the corresponding row's ColumnA value is not found in ColumnB. A number indicates the row in ColumnB that matches the ColumnA value for the row the value appears in. ...

Are you looking for excel formulas to do this for you? How about: =VLOOKUP((LEFT(A1, 5)), Sheet2!A:C, 2, FALSE) In column B of Sheet1. A1 references the first column in sheet one, and you would change the 2 to a 3 in column C....

excel,range,criteria,formula,countif

You could use SUMPRODUCT instead, where you can use ranges: =SUMPRODUCT(--(A:A<B:B-120)) If A:A is smaller than B:B after removing 120 days, then (A:A<B:B-120) will return true for each row where this applies, and false otherwise. -- at the start will convert those to 1s and 0s respectively and SUMPRODUCT add...

If your SUMIFS() function works, just create a COUNTIFS() function in a cell next to it. =COUNTIFS(E2:E4991,">="&G$167,E2:E4991,"<="&G$168) ...

excel,pivot-table,percentage,worksheet-function,countif

If your results are in Column A for example, then something like: ="Less than $1 million: "&(COUNTIF(A:A,"Less than $1 million")/COUNTA(A:A))*100&"%" should do you. If you have a header in Row 1 then change COUNTA(A:A) to COUNTA(A:A)-1 based on your example above, this formula would return "Less than $1 million: 62.5%"...

You just need to use COUNTIFS() to add extra criteria to when to count something. Using the following data: Region Age 2 12 2 16 2 41 1 62 3 26 3 50 2 12 1 65 3 64 Where Region = B1 we can use =COUNTIFS(A2:A10,2,B2:B10,12) to count the...

excel,excel-formula,excel-2013,countif,excel-indirect

The INDIRECT function returns a reference to a range. You can use this function to create a reference that won't change if row or columns are inserted in the worksheet. Or, use it to create a reference from letters and numbers in other cells. =COUNTIFS(cancellations!AG2:AG408,">0",cancellations!AG2:AG408,B1) should work where B1 contains...

excel,excel-formula,worksheet-function,excel-2013,countif

Ugly, but might suit: =COUNTIFS(E:E;">120";E:E;"<139")+COUNTIFS(F:F;">80";F:F;"<89")-COUNTIFS(E:E;">120";E:E;"<139";F:F;">80";F:F;"<89") Counts all the applicable instances in ColumnE, adds the count of all the applicable instances in ColumnF then subtracts the instances where both ColumnE and ColumnF are within the respective bounds - this last part might not be required. An array formula might be more...

excel,excel-formula,worksheet-function,countif,array-formulas

Just wanted to check if @Byron answer in the comment works. It works, the result is 7. {=SUMPRODUCT(--(A2:A17>128),--(A2:A17<136), --(NETWORKDAYS(IF(ISBLANK(C2:C17),B2:B17,C2:C17)+0,TODAY()+0,0)<=30))} ...

excel,macros,count,frequency,countif

Assuming the data as you give it is in A1:B18 (with headers in row 1), enter this in B2: =IF(A1<>A2,MATCH(TRUE,INDEX(A2:A$1000<>A2,),)-1,"") Copy down as required. Amend the 1000 to a sufficiently higher row reference if necessary. Regards...

You can do =COUNTIFS(B3:B62, ">=1/1/2014", B3:B62, "<2/1/2014") Microsoft Documentation here...

excel,excel-formula,worksheet-function,countif

You could add multiple countif's - =if(A1<>"",countif(a2:a5,"Yes")+countif(a2:a5,"Si")+countif(a2:a5,"True"),"")

arrays,excel,vba,excel-vba,countif

You have several problems. Using CountIf First, if I understand right, you are intentially trying to use the Application.WorksheetFunction.CountIf statement on an array. That will only cause trouble, since CountIf (as the statment suggests) is a "worksheet function" not a "VBA Array function". Ditto has created a solution that uses...

excel,excel-formula,worksheet-function,countif

To count any entries use: =COUNTA(F8:F20) To count specific entries use: =COUNTIF(F8:F20,"W") ...

search,nested,excel-formula,countif

I'm not sure why you are getting 7.37961E-05, but assuming you have something like "123 Total" in B54 you need to take the IF statement outside the rest of the formula and test for the presence of the string "Total" in B54 first:- =IF(ISNUMBER(SEARCH("Total",B54,1)),(D54)/COUNTIF(B:B,LEFT(B54,SEARCH("Total",B54,1)-2)),"") It can still give a #DIV/0!...

excel,excel-formula,worksheet-function,conditional-formatting,countif

Maybe add an AND to your formula: =AND($G1="name",COUNTIF($M1:$N1,M1)>1) ...

excel,sum,excel-formula,worksheet-function,countif

Hopefully: =COUNTIFS(A:A,"O",B:B,"<>"&"O")+COUNTIFS(A:A,"R",B:B,"<>"&"R") For equal: =COUNTIFS(A:A,"O",B:B,"O")+COUNTIFS(A:A,"R",B:B,"R") ...