+ Reply to Thread
Results 1 to 9 of 9

Sum product based on Multiple Criteria

  1. #1
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2016
    Posts
    387

    Sum product based on Multiple Criteria

    Hi,

    I need help in solving the SumProduct formula based on multiple criteria

    Start Date
    End Date
    Acct. No
    Font Colour
    Attached Files Attached Files

  2. #2
    Forum Moderator 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: Sum product based on Multiple Criteria

    Hi,

    Try this function

    Please Login or Register  to view this content.
    Name
    B41 'dtStart'
    B42 'dtEnd
    G42 'fontcolour

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,277

    Re: Sum product based on Multiple Criteria

    Try this in C43 thendragged across

    =SUMPRODUCT(((A2:A37<=B41)+(A2:A37>=B42))*(E2:E37=B43)*F2:F37)
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2016
    Posts
    387

    Re: Sum product based on Multiple Criteria

    hi Richard,

    How can get the result using you Function

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,277

    Re: Sum product based on Multiple Criteria

    Try this in C43 thendragged across

    =SUMPRODUCT((($A$2:$A$37<=$B$41)+($A$2:$A$37>=$B$42))*($E$2:$E$37=$B43)*F2:F37)
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 11-22-2018 at 11:11 AM.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,896

    Re: Sum product based on Multiple Criteria

    I think this is what you need:

    =SUMPRODUCT(--($A$2:$A$37>=$B$41)*($A$2:$A$37<=$B$42)*($E$2:$E$37=$B43)*TestFont($F$2:$F$37,$G$42)*F$2:F$37)

    in C43, copied across and down.
    Attached Files Attached Files
    Glenn



  7. #7
    Forum Moderator 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: Sum product based on Multiple Criteria

    Put the Function in a VBA Module.

    Alt-F11 to go to VBA, find your workbook in the VBA Project window on the left, choose one of the objects - a sheet or the workbook object and from the menu choose Insert Module and copy the code I gave.

    Then in the worksheet treat that function as a normal Excel function and enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See workbook attached
    Attached Files Attached Files
    Last edited by Richard Buttrey; 11-22-2018 at 12:43 PM.

  8. #8
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2016
    Posts
    387

    Re: Sum product based on Multiple Criteria

    Hi Glenn,

    Thanks, at present I am out of office. I will check and get back.

  9. #9
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2016
    Posts
    387

    Re: Sum product based on Multiple Criteria

    Thank you Richard & Glenn, excellent. Solved..

+ 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. product of two cells based on a criteria like sum.if
    By lord anubis in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-06-2018, 10:31 AM
  2. Need to product based on a criteria and then want to sum the whole range
    By aparnawangu in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-09-2018, 01:54 PM
  3. [SOLVED] Sum Product for multiple criteria
    By lejanco in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-30-2014, 11:25 AM
  4. multiple column sum product based on set criteria
    By b16dlg in forum Excel General
    Replies: 4
    Last Post: 07-11-2012, 07:51 AM
  5. sum product w/ multiple criteria
    By TechRetard in forum Excel General
    Replies: 4
    Last Post: 09-06-2011, 03:32 PM
  6. Multiple criteria for Sum-Product
    By thelucasgray in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-05-2010, 09:27 PM
  7. [SOLVED] Product of 2 arrays based on criteria
    By Ben010 in forum Excel General
    Replies: 4
    Last Post: 03-20-2006, 03:55 PM

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