1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Need Excel Formula

Discussion in 'Business Applications' started by wilj0069, Aug 29, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. wilj0069

    wilj0069 Thread Starter

    Joined:
    Aug 29, 2007
    Messages:
    17
    I need a formula that will lookup between 2 given date ranges and return a sum of numbers in a specific column. For example, I want to sum all dividends for symbol EEF in between the dates of 12/29/2006 and 3/13/2007. I have a sheet set up with all symbols, dates, and dividends that are needed, but i can't figure out a lookup function to only return the values over a given range of time. I hope this makes sense. Thank You

    Brandon
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there, welcome to the board!

    You could use this ...

    =SUMPRODUCT((A2:A20=F4)*(B2:B20>=F2)*(B2:B20<=F3),C2:C20)

    This assumes that your symbols are in A2:A20, dates are in B2:B20, and dividends are in C2:C20. Furthermore, the criteria is assumes to be start date in F2, end date in F3, and symbol criterion in F4.

    HTH
     
  3. wilj0069

    wilj0069 Thread Starter

    Joined:
    Aug 29, 2007
    Messages:
    17
    I am sort of understanding this, however it doesn't quite work with the set up I have. I have the symbols from B-Z across the top. I have the dates from A1-A100 lets say down the side and the dividends for each date and symbol in the matching box accordingly. Is there anyway you can help with that? Thank you in advance
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    The ranges need to be of the same size here for this to work. Is there any way you can post a sample of your file for us to work with?
     
  5. wilj0069

    wilj0069 Thread Starter

    Joined:
    Aug 29, 2007
    Messages:
    17
    This is one of about 100 symbols I have on this sheet. Mabe it will help.
     

    Attached Files:

  6. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Not much information - it looks as if you just are needing a simple SUM of the data in column B (e.g., to get the sum of the data from 12/29/2006 to 3/13/2007 you would put somewhere - say C1 - the formula =SUM(B2:B50) ). But I suspect that is not all you want to do?
     
  7. wilj0069

    wilj0069 Thread Starter

    Joined:
    Aug 29, 2007
    Messages:
    17
    No I have 100 of those and on a separate project I want to be able to put in a date range (12/29/2006 and 3/13/2007) and a symbol (IEF) and have excel sum all the dividends in that given time period. I don't know if it is a lookup function or what. I used a matching formula to find a single cell to match the date and symbol to a price, but am looking for someway to use that and sum all the dividends in that date range. Here is a larger sample to help you better understand.
     

    Attached Files:

  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    You can use this formula...

    =SUMIF($A$2:$A$84,">="&$Q$1,OFFSET($A$2,0,MATCH($Q$3,$B$1:$N$1,0)):OFFSET($A$84,0,MATCH($Q$3,$B$1:$N$1,0)))-SUMIF($A$2:$A$84,">"&$Q$2,OFFSET($A$2,0,MATCH($Q$3,$B$1:$N$1,0)):OFFSET($A$84,0,MATCH($Q$3,$B$1:$N$1,0)))


    You'll need some values in the cells above, start date in Q1, end date in Q2, symbol in Q3. I'll upload your sample revised with the formula.

    HTH
     

    Attached Files:

  9. wilj0069

    wilj0069 Thread Starter

    Joined:
    Aug 29, 2007
    Messages:
    17
    Thank you very much. This is exactly what I needed.
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Sure, no problem.

    One thing I forgot to ask, would this list of your be growing in either direction? If so, you may want to think about making this dynamic.
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/616930

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice