Replication Excel SUMIFS Formulas

Question

I need to replicate - or at least find an alternative solution - for the SUMIFS function that I have in Excel.

I have a transactional database:

SegNbr Index Revenue SUMIF A 1 10 30 A 1 20 30 A 2 30 100 A 2 40 100 B 1 50 110 B 1 60 110 B 3 70 260 B 3 80 260 

and I need to create another column that sums the revenue, by Segment number, for all indices that are equal to or less than the Index on this row. This is a distorted moving income, because it will be the same for each SegmentNumber / Index key. This is the formula:

 =SUMIFS([Revenue],[SegNbr],[@SegNbr],[Index],"<="&[@Index]) 

Answer 1

Let's say you have this sample data.frame

 dd<-read.table(text="SegNbr Index Revenue A 1 10 A 1 20 A 2 30 A 2 40 B 1 50 B 1 60 B 3 70 B 3 80", header=T) 

Now, if we are sure that the data is ordered by segment and index, we can do

 dd<-dd[order(dd$SegNbr, dd$Index), ] #sort data dd$OUT<-with(dd, ave( ave(Revenue, SegNbr, FUN=cumsum), #get running sum per seg interaction(SegNbr, Index, drop=T), FUN=max, na.rm=T) #find largest sum per index per seg ) dd 

This gives

  SegNbr Index Revenue OUT 1 A 1 10 30 2 A 1 20 30 3 A 2 30 100 4 A 2 40 100 5 B 1 50 110 6 B 1 60 110 7 B 3 70 260 8 B 3 80 260 

optional.