+ Reply to Thread
Results 1 to 8 of 8

Sumproduct formula problem

Hybrid View

  1. #1
    Registered User
    Join Date
    06-03-2008
    Posts
    7

    Exclamation Sumproduct formula problem

    Hello all,
    I have a database that has named column ranges "terr, type, date, & time"
    I want to sum all the values in the "time" range that match the terr#, Type, and between the two dates of Startdate and Enddate.

    The formula below works but comes back with a very low amount so its either not summing the time and only counting instances or ????

    I do not know. Help
    Thanks for any help you can give....

    =SUMPRODUCT(--(Terr=1),--(Type="Regular Call Cycle"),--(INT(date)>=StartDate),--(INT(date)<=EndDate),Time)

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    The things I would question are:

    1. Are you sure your named ranges cover all the database records?

    2. Are the values in Terr all formatted correctly as numbers? Go to Data|Text to Columns and click Finish... should change all to numbers...

    3. Are the supposed matches in Type exact matches? I.e. no extra spaces, chars, etc in any of the fields.

    If still not right, attach the spreadsheet for us to review.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    06-03-2008
    Posts
    7

    Post

    Here is the sheet that is causing me the grief....

    Thanks all....
    Attached Files Attached Files

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    The formula you have in the spreadsheet does not look like the formula you posted originally in this thread.

    Here is what you have in the spreadsheet:

    =SUMPRODUCT((terr=1),--(type="Regular Call Cycle"),--INT(date)>=startdate)--(INT(date)<=enddate)*(time)
    You have missing --, you have missing commas, you have missing opening parentheses, etc....

    The formula you posted originally, when pasted in your sheets results in 765

    =SUMPRODUCT(--(Terr=1),--(Type="Regular Call Cycle"),--(INT(date)>=StartDate),--(INT(date)<=EndDate),Time)

  5. #5
    Registered User
    Join Date
    06-03-2008
    Posts
    7
    Sorry, I was experimenting with the formula so the one in the sheet might have been different.
    The formula works great and since you fixed up the missing elements I included the index functions and it now works the way I need it to.

    =SUMPRODUCT(--(INDEX(data,,2)=B10),--(INDEX(data,,10)="Regular Call Cycle"),--(INT(INDEX(data,,5))>=startdate),--(INT(INDEX(data,,5))<=enddate),INDEX(data,,14))

    How about the other formula to Count of all unique location#s, between the start and end date, and match terr#?? Is this even possible in a formula?

    If it is, I need to learn much more about advanced formulas.

    Thanks a million........ O

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Not sure which formulas you are talking about...looks like the other formulas are similar and are yielding results.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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