+ Reply to Thread
Results 1 to 5 of 5

SUMPRODUCT with date restrictions

  1. #1
    Registered User
    Join Date
    03-09-2012
    Location
    Belgium
    MS-Off Ver
    2007
    Posts
    14

    SUMPRODUCT with date restrictions

    I am trying to keep track of trips for my records. I need some help doing so by using the SUMPRODUCT formula. I have it able to count the total number of times a state appears in column B, even if I went to two states in one trip, it counts as 1 trip. However, I am trying to further track that by years of the trips. Any help would be appreciated. I have attached an example to show what I want and what I have so far.
    Attached Files Attached Files

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: SUMPRODUCT with date restrictions

    Hi

    One way, could be this.

    In J3 and in K3, type the States that you are looking for.

    In D3 and copy down, use this.

    Please Login or Register  to view this content.
    ThiS is a helper column and you can hide this.

    Then in K4, you get your result, using this.

    Please Login or Register  to view this content.
    Is this works for you?
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: SUMPRODUCT with date restrictions

    Try this in K4
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Or for 2007 and above
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    However I'm not sure it is wise to use SUMPRODUCT() with whole columns.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  4. #4
    Registered User
    Join Date
    03-09-2012
    Location
    Belgium
    MS-Off Ver
    2007
    Posts
    14

    Re: SUMPRODUCT with date restrictions

    okay so I may have miss lead you a little. This did help and bring me closer to my goal. However, I am alo trying to track the trips by region, year and quarter. Can anyone help with this matter? I have attached a better workbook that shows my goals.
    Attached Files Attached Files

  5. #5
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: SUMPRODUCT with date restrictions

    You're making life hard with your sheet layout and data types, concatenating initial data is seldom a good idea, nor is merging cells.

    This is a bit of a guddled compromise, see if it returns the results you want.
    If it does we can try to simplify the process.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1