Results 1 to 5 of 5

Thread: SUMPRODUCT - Sum Count of Multiple Criteria met

  1. #1
    Acolyte Sam's Avatar
    Join Date
    Apr 2014
    Posts
    36
    Articles
    0
    Excel Version
    2007

    SUMPRODUCT - Sum Count of Multiple Criteria met



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,

    I am using Excel 2007 for Windows. I would like some assistance with using the SUMPRODUCT Function in a worksheet based formula.

    I have three criteria to meet, and then sum the total count of the criteria met. I am using dynamic named ranges (two of them span multiple columns):
    Range1 – 8 columns wide, length dynamic, cells contain numerical constants
    Range2 – 8 columns wide, length dynamic–same as Range1, cells contain numerical constants
    Range3 – 1 column wide, length dynamic–same as Range1, cells contain numerical constants.

    Scenario:
    Defined range names:
    Row 13 is a column header name
    Range1 =OFFSET(Sheet1!$H$13,1,0,COUNT(Sheet1!$H:$H),8) - workbook level name
    Range2 =OFFSET(Sheet1!$P$13,1,0,COUNT(Sheet1!$P:$P),8) - workbook level name
    Range3 =OFFSET(Sheet1!$E$13,1,0,COUNT(Sheet1!$E:$E),1) - sheet level name

    Range1 is a store reference, Range2 is a product code, and Range3 is a day of the month.
    For example, I would like to know the summed count when store 1 (in Range1) has sold product 223 (in Range2) on 31st day, represented as 31 (in Range3).

    Attempted formulas - not working:
    =SUMPRODUCT((Range1=1)*(Range2=223)*(Sheet1!Range3=31))

    =SUMPRODUCT(--(MMULT((Range1=1)*(Range2=223)*( Sheet1!Range3=31),{1;1;1;1;1;1;1;1})))

    The formula will be copied across 60 columns and down 31 rows. I would appreciate the most efficient Function and syntax to meet my requirements.

    Hope you can help.

    Thank you,
    Sam

  2. #2
    The first formula works fine in my test. Can you post the workbook?

  3. #3
    Acolyte Sam's Avatar
    Join Date
    Apr 2014
    Posts
    36
    Articles
    0
    Excel Version
    2007
    Hi Bob,

    Thank you for reply, and taking the time to test the formula.

    Quote Originally Posted by Bob Phillips View Post
    The first formula works fine in my test. Can you post the workbook?
    No, it's a work file and I don't have permission to post it. When I use only two of the three criteria together, in various combinations, I get a total. However, when I use all three of the criteria the formula returns zero. I have used ISNUMBER to test cells in each column to make sure that they are numerical values. Can you think of anything I can check or do?


    Thank you,
    Sam

  4. #4
    Can't you knock up an example workbook that shows the problem?

  5. #5
    Acolyte Sam's Avatar
    Join Date
    Apr 2014
    Posts
    36
    Articles
    0
    Excel Version
    2007
    Bob,

    Quote Originally Posted by Bob Phillips View Post
    Can't you knock up an example workbook that shows the problem?
    The formula wasn't returning the expected results because I was using an incorrect range. Thank you for your time.
    Thank you,
    Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •