+ Reply to Thread
Results 1 to 8 of 8

Sumproduct formula problem

  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:

    Please Login or Register  to view this content.
    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

    Please Login or Register  to view this content.

  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.

  7. #7
    Registered User
    Join Date
    06-03-2008
    Posts
    7
    It would be the one that counts the unique # of calls by territory for the data range. It is the one in Yellow with "help" in the cells. A description of it is at the bottom in Yellow. The location numbers that need to be unique is located in the range "Data" in column 1 or INDEX(data,,2)

    I am not sure if it is even possible in a formula considering the unique records only criteria.

    If it is, I will become an Excel worshiper
    Last edited by sclang; 06-04-2008 at 10:18 AM.

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Can you help me understand how exactly you got the sample results you already have in that column?

    for example, how did you get 23 in cell C10?

+ 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