Results 1 to 7 of 7

Thread: SUMIF function, need guru help

  1. #1

    SUMIF function, need guru help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,467
    Articles
    0
    Excel Version
    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.....


  3. #3
    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!
    Attached Files Attached Files

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,467
    Articles
    0
    Excel Version
    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 by NBVC; 2013-04-19 at 02:09 PM.


  5. #5
    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

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Wa4441
    I think that you have posted this against an unrelated thread.

  7. #7
    Sorry new at this...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •