SUMIF function, need guru help

daniel

New member
Joined
Apr 18, 2013
Messages
4
Reaction score
0
Points
0
Hello Excel-Guru's,

I am quite new to excel and therefore I need some help!

I have 2 sheets. On the 1st sheet is one column with company names, and 2 columns with dates. On the next sheet are daily returns of these companies: company names in the first column, with the daily return of the company horizontally next to it arranged by date. On top there is a line with the individual dates corresponding with the daily returns.

What I want to do is take the 2 dates in the first sheet, and sum up the corresponding daily returns of the stocks in the 2nd sheet. It has something to do with the Sumif function if I'm correct but I cannot figure it out. I was not able to find anything on the excelguru forum with this specific problem!
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Assuming your daily returns sheet is called Sheet2 and data is in A1:N20, including column/row headers, then assuming first customer is in active sheet in A2, with corresponding dates in B2 and C2, try this formula:

=SUMIFS(INDEX(Sheet2!$B$2:$N$20,MATCH(A2,Sheet2!$A$2:$A$20,0),0),Sheet2!$B$1:$N$1,">="&B2,Sheet2!$B$1:$N$1,"<="&C2)

adjusting ranges to suit. Where A2:A20 contain company names, and B1:N1 contain dates of returns, and B2:N20 contain all the corresponding returns.....
 

daniel

New member
Joined
Apr 18, 2013
Messages
4
Reaction score
0
Points
0
I have an example worksheet since my first explanation was a bit unclear. NVBC, thank you for the post, but I was not able to figure it out!
 

Attachments

  • Workbook1.xlsx
    30.6 KB · Views: 18

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
I guess I had a different picture in my head from your description....

Try:

=SUMPRODUCT((Sheet2!$A$1:$BH$1>=A2)*(Sheet2!$A$1:$BH$1<=B2)*ISNUMBER(Sheet2!$A$2:$BH$10),Sheet2!$A$2:$BH$10)

or

=SUM(INDEX(Sheet2!$A$2:$BH$10,0,MATCH(A2,Sheet2!$A$1:$BH$1,0)):INDEX(Sheet2!$A$2:$BH$10,0,MATCH(B2,Sheet2!$A$1:$BH$1,0))) which I think would be actually more efficient if you have a large database.
 
Last edited:

wa4441

New member
Joined
May 10, 2013
Messages
3
Reaction score
0
Points
0
Hi I need help, simple calculation
JanFebMarAprMayJunJulAugSepOctNovDecTotal
Salary2468743234565772476

Need simple formula to calculate Mar YTD number referencing a changeable cell (exp, May, Dec)
Hope you can help
 
Top