+ Reply to Thread
Results 1 to 4 of 4

Using SUMPRODUCT and different variables

  1. #1
    Registered User
    Join Date
    07-25-2011
    Location
    Midlands, England
    MS-Off Ver
    Excel 2013
    Posts
    32

    Using SUMPRODUCT and different variables

    I’m currently using SUMPRODUCT to get the required result. I have a working formula with the correct results and now wish to add an extra search criteria.

    My basis is this, which picks out all the content in Column P and adds up all those in the month of July:
    =SUMPRODUCT(NOT(ISBLANK('[MS5]MS5'!P5:P1000))*(MONTH('[MS5]MS5'!P5:P1000)=7))

    I now want to add the criteria into this where it only adds up based on B5:B1000 being equal to 1 (1 being a product code, rather than an actual number).

    I believe this is simple, but for some reason it has stumped me today!
    Last edited by highguyuk; 07-19-2012 at 10:40 AM.

  2. #2
    Forum Contributor BenMiller's Avatar
    Join Date
    12-06-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Using SUMPRODUCT and different variables

    Try
    =SUMPRODUCT(--(NOT(ISBLANK('[MS5]MS5'!P5:P1000))),--(MONTH('[MS5]MS5'!P5:P1000)=7),--('[MS5]MS5'!B5:B1000=1))

  3. #3
    Registered User
    Join Date
    07-25-2011
    Location
    Midlands, England
    MS-Off Ver
    Excel 2013
    Posts
    32

    Re: Using SUMPRODUCT and different variables

    That's great, thanks Ben.

    I'm assuming now I can continue to add further search criteria if desired in a similar fashion - or is there a limit?

  4. #4
    Forum Contributor BenMiller's Avatar
    Join Date
    12-06-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: Using SUMPRODUCT and different variables

    Don't think there's a limit! As long as the ranges are the same sizes.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1