+ Reply to Thread
Results 1 to 10 of 10

Sumifs to excel 2003 trouble

  1. 07-19-2011, 09:56 PM #1
    bigkp
    bigkp is offline
    Registered User
    Join Date
    07-19-2011
    Location
    Saskatchewan, Canada
    MS-Off Ver
    Excel 2003
    Posts
    4

    Question Sumifs to excel 2003 trouble

    I am trying to convert a =sumifs formula from excel 2007 into excel 2003. I have tried to use sumproduct and sum(if(( ... but both do not like the fact that I am comparing to a date (if the date in an aray field is greater then value one (a cell) and less than another (another cell) and the employee name matches then it adds up the ammount of time. I have attached the excel 2007 formula. When I use either of the two methods above it returns a #num error and the erro checking points to the two referece date cells.

    Any help is certainly appreciated!!

    =_xlfn.SUMIFS('Time Sheet Records'!K:K,'Time Sheet Records'!D:D,">"&'Payroll Calculations'!$M$3,'Time Sheet Records'!C:C,'Payroll Calculations'!C6,'Time Sheet Records'!D:D,"<"&('Payroll Calculations'!$M$4+1))

    Column K:K - hours
    Column D:D - date
    Column C:C - name

    Table currently has over 8000 records and rising - 30K - 40K final estimate
    Last edited by bigkp; 07-19-2011 at 09:59 PM.
    Reply With QuoteRegister To Reply
  2. 07-19-2011, 10:56 PM #2
    sailepaty
    sailepaty is offline
    Valued Forum Contributor
    Join Date
    02-08-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: Sumifs to excel 2003 trouble

    Try this, but It will be better if you create a dinamic range.

    =SUMPRODUCT(--('Time Sheet Records'!C1:C50000='Payroll Calculations'!C6),--('Time Sheet Records'!D1:D50000>'Payroll Calculations'!M3),--('Time Sheet Records'!D1:D50000<'Payroll Calculations'!M4+1),'Time Sheet Records'!K1:K50000)

    Regards
    Reply With QuoteRegister To Reply
  3. 07-19-2011, 11:28 PM #3
    bigkp
    bigkp is offline
    Registered User
    Join Date
    07-19-2011
    Location
    Saskatchewan, Canada
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Sumifs to excel 2003 trouble

    I still get the #num! error. When I trace the error it points to the reference cells M3, M4 and C6. Any thoughts?
    Reply With QuoteRegister To Reply
  4. 07-19-2011, 11:38 PM #4
    thepdaoson
    thepdaoson is offline
    Registered User
    Join Date
    06-17-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    42

    Re: Sumifs to excel 2003 trouble

    Please upload your file, it should be easier. Your case can use formular like that:
    = sumproduct((K1:K20> $M$3)*(D1:D20>$M$3)*(C1:C20=$M$3)).
    If the function has more criteria, you just create more array.
    Reply With QuoteRegister To Reply
  5. 07-19-2011, 11:41 PM #5
    sailepaty
    sailepaty is offline
    Valued Forum Contributor
    Join Date
    02-08-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: Sumifs to excel 2003 trouble

    Quote Originally Posted by bigkp View Post
    I still get the #num! error. When I trace the error it points to the reference cells M3, M4 and C6. Any thoughts?
    Did you use the ranges I suggested or the complete columns?

    Regards
    Reply With QuoteRegister To Reply
  6. 07-19-2011, 11:44 PM #6
    thepdaoson
    thepdaoson is offline
    Registered User
    Join Date
    06-17-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    42

    Re: Sumifs to excel 2003 trouble

    No, that is just my example. Please upload your file or similar template, so that I can do for you.
    Reply With QuoteRegister To Reply
  7. 08-03-2011, 08:28 PM #7
    bigkp
    bigkp is offline
    Registered User
    Join Date
    07-19-2011
    Location
    Saskatchewan, Canada
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Sumifs to excel 2003 trouble

    This may sound dumb, buit how do I upload it to you?

    Cheers
    Reply With QuoteRegister To Reply
  8. 08-03-2011, 08:41 PM #8
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Sumifs to excel 2003 trouble

    buit how do I upload it to you?
    The forum rules explain everything you need to know.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  9. 08-03-2011, 09:16 PM #9
    bigkp
    bigkp is offline
    Registered User
    Join Date
    07-19-2011
    Location
    Saskatchewan, Canada
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Sumifs to excel 2003 trouble

    The file upload keeps giving me a database error - any suggestions?

    Thanks
    Reply With QuoteRegister To Reply
  10. 08-03-2011, 11:29 PM #10
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Sumifs to excel 2003 trouble

    Only to keep trying. The forum is not well maintained by the owners.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1