+ Reply to Thread
Results 1 to 7 of 7

sumproduct muitiple criteria with greater than function

  1. #1
    Registered User
    Join Date
    12-22-2015
    Location
    New Jersey
    MS-Off Ver
    MS Office
    Posts
    3

    sumproduct muitiple criteria with greater than function

    I am using a sumproduct formula with multiple criteria. I want the formula to return the sum total of values greater than a specific number. I believe my return is giving me the count vs he sum total. Where am I going wrong with my formula?

    enicMF
    ----B----------C------------D-------------F-------Result
    --0001-------CF-------New Jersey-----160-----700000
    --0001-------CF-------New York-------160-------0---


    Q111301B
    ----A----------J------------D-------------F---------G---
    --0001-------CF-------New Jersey-----160-----300000
    --0001-------CF-------New Jersey-----160-----400000
    --0001-------CF-------New York-------160------200000
    --0001-------CF-------New York-------160------150000

    My formula is:

    =SUMPRODUCT((Q111301B!$A$2:$A$18296=enicMF!B532)*(Q111301B!$J$2:$J$18296=enicMF!C532)*(Q111301B!$D$2:$D$18296=enicMF!D532)*(Q111301B!$F$2:$F$18296+0=enicMF!F532)*(Q111301B!$G$2:$G$18296>500000))

    Where is I going wrong?

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,886

    Re: sumproduct muitiple criteria with greater than function

    Try this:
    Please Login or Register  to view this content.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,189

    Re: sumproduct muitiple criteria with greater than function

    You'd need something like:

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    12-22-2015
    Location
    New Jersey
    MS-Off Ver
    MS Office
    Posts
    3

    Re: sumproduct muitiple criteria with greater than function

    That formula correction worked in picking up values greater than 500000 but how can I fix my formula where I want to add all the values that meet my criteria and only give me the total if the value is greater than 500000. I'm hoping it's only a minor correction

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,886

    Re: sumproduct muitiple criteria with greater than function

    Try this:
    Please Login or Register  to view this content.
    Let me know if you have any questions.

  6. #6
    Registered User
    Join Date
    12-22-2015
    Location
    New Jersey
    MS-Off Ver
    MS Office
    Posts
    3

    Re: sumproduct muitiple criteria with greater than function

    JeteMc thank you so much. You are absolutely awesome!!

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,886

    Re: sumproduct muitiple criteria with greater than function

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. Hope that you have a good day.

+ 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 FUNCTION with multiple criteria
    By megtoma in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-13-2014, 04:39 PM
  2. Adding a third criteria to Sumproduct function
    By superboy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-06-2014, 05:01 AM
  3. Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text
    By Groovicles in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 08-19-2013, 11:11 AM
  4. [SOLVED] Sumproduct Function with greater or less than formula?
    By excellenthelp in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-16-2013, 03:17 PM
  5. [SOLVED] LookUP formula matching muitiple conditions
    By Excel Dumbo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-08-2013, 08:56 PM
  6. Index Matching with a nested Greater than function for multiple criteria
    By Solpics in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-05-2012, 12:22 AM
  7. Sumproduct Function Using 4 Different Criteria
    By LisaG in forum Excel General
    Replies: 2
    Last Post: 09-17-2008, 07:56 AM
  8. [SOLVED] Using Sumproduct Function To Add Multiple Criteria
    By Ange Kappas in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2006, 07:30 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