# SUMIF function, need guru help

#### daniel

##### New member
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
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
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

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
Hi I need help, simple calculation
 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Salary 2 4 6 87 4 3 2345 6 5 7 7 2476

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

#### Hercules1946

##### New member
Wa4441
I think that you have posted this against an unrelated thread.

#### wa4441

##### New member
Sorry new at this...