+ Reply to Thread
Results 1 to 4 of 4

sumproduct calculation

  1. #1
    Forum Contributor
    Join Date
    11-03-2004
    Posts
    139

    sumproduct calculation

    I am trying to find out how many associates at the end of the year based on a job category.
    My formula is:
    "=SUMPRODUCT(--(Hire_Date<="12/31/2000"+0),((Termed>="1/1/2000"+0)+(Termed="")),--((A2="")+(Region=A2)>0),--((B2="")+(SVCCTR=B2)>0),--((C2="")+(Job_Category=C2)>0),--((D2="")+(Status=D2)>0))"

    I have 60 total and 8 were termed so my formula should give me 52. But the above formula gives me 60. I have attached a sample for a better understanding. I am stumped in what the problem is. Can you please what is wrong?

    Thanks in advance for your expert knowledge and help.
    Michelle
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: sumproduct calculation

    Hi,

    I'm not exactly sure why you've got dates as strings or indeed why you want to have a + 0, and when I open the file not unsurprisingly the formulae return errors

    Does the following construct help you. It returns 8 for the 2000 termed values.
    Incidentally, and particularly with array formulae like SUMPRODUCT(), try and avoid using ranges that are larger than you actually need. I note all your ranges are down to row 65536

    Please Login or Register  to view this content.
    No doubt you can expand on that for your other requirements.

    Regards
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    11-03-2004
    Posts
    139

    Re: sumproduct calculation

    I think you are giving me the 8 that are termed but what I am looking for is the 60 should really be 52 in cell B4. Total number-termed. I am attaching a better sample. Will you please take another look? I will take your suggestion on making the ranges smaller.
    Attached Files Attached Files

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: sumproduct calculation

    Hi,

    Try

    Please Login or Register  to view this content.
    Regards

+ 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