+ Reply to Thread
Results 1 to 11 of 11

Dynamic sumifs formula to be converted with sumproduct formula for use in excel 2003

  1. #1
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Dynamic sumifs formula to be converted with sumproduct formula for use in excel 2003

    Hi All

    See attached file, need same technique but using sumproduct instead of sumifs .

    need this as certain of my peers are using excel 2003.

    Thanks if any anyone can help.

    Attached Files Attached Files

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Dynamic sumifs formula to be converted with sumproduct formula for use in excel 2003

    Please Login or Register  to view this content.
    You might also want to check the differences in tables between 2003 and 2007.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Dynamic sumifs formula to be converted with sumproduct formula for use in excel 2003

    I'm don't think that XL2003 had structured tables, so put this formula in T2:

    =SUMPRODUCT($L$2:$L$428,($B$2:$B$428=$Q2)*($M$2:$M$428=T$1))

    then copy across and down. Don't forget to save the file as a 97-2003 compatible (i.e. with .xls extension).

    Hope this helps.

    Pete

  4. #4
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Dynamic sumifs formula to be converted with sumproduct formula for use in excel 2003

    Maybe a pivot table is an alternative way.
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  5. #5
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Re: Dynamic sumifs formula to be converted with sumproduct formula for use in excel 2003

    Hi
    Try your method , not well aquainted with pivot table, try by myself well very good this method

    Thanks

  6. #6
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Re: Dynamic sumifs formula to be converted with sumproduct formula for use in excel 2003

    Hi
    Try your formula , works very well,but could you help me for column R Amount with sumproduct formula

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Dynamic sumifs formula to be converted with sumproduct formula for use in excel 2003

    Not sure who you are addressing that last comment to.

    Pete

  8. #8
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Re: Dynamic sumifs formula to be converted with sumproduct formula for use in excel 2003

    Hi Thanks , save file in excel 2003 format,however could advise for column amount how to use sumproduct according invoices

  9. #9
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Re: Dynamic sumifs formula to be converted with sumproduct formula for use in excel 2003

    Sorry it was addressed to Pete and Ben_Hensel for column amount a solution formula for column Amount using sumproduct

  10. #10
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Dynamic sumifs formula to be converted with sumproduct formula for use in excel 2003

    It's really easy?

    existing:
    Please Login or Register  to view this content.
    Sumproducts notation:
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Re: Dynamic sumifs formula to be converted with sumproduct formula for use in excel 2003

    HI BEN

    Do agree I feel but at least I have learned a bit more on sumproduct.

    Thanks for your help

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sumproduct with sumifs formula
    By nilani in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-22-2014, 08:50 AM
  2. SUMIFS formula not working in Excel 2003
    By Sc0tt1e in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-10-2014, 07:34 AM
  3. [SOLVED] Why my SUMPRODUCT() formula does not work in Excel 2003?
    By billj in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-09-2013, 07:04 PM
  4. [SOLVED] Excel 2003 Help with sumproduct formula to sort by month and size
    By cajodonn in forum Excel General
    Replies: 3
    Last Post: 06-25-2012, 09:50 AM
  5. Replies: 8
    Last Post: 11-14-2011, 07:45 PM
  6. Dynamic Sumifs Formula
    By skycowboy in forum Excel General
    Replies: 3
    Last Post: 03-14-2011, 11:22 AM
  7. Replies: 10
    Last Post: 02-28-2011, 03:26 AM

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