+ Reply to Thread
Results 1 to 4 of 4

Multi criteria sumproduct name range query

  1. #1
    Registered User
    Join Date
    03-15-2011
    Location
    Dubai
    MS-Off Ver
    Excel 2003
    Posts
    3

    Multi criteria sumproduct name range query

    Hello,

    Would highly like if you could shed some light on how I can accomplish this.

    My sheet has three segments. The first being a selection for the users (the user can choose which category they want to view the reports for):
    Selection
    # Category Include?
    1 Electricals Yes
    2 Paper Yes
    3 Food Yes
    4 Beverages No
    5 Pets No
    6 Cosmetics No

    The second segment has sales records

    Sales
    Year Month Category Quantity
    2010 1 Electricals 5
    2010 1 Paper 434
    2010 2 Electricals 23
    2010 2 Food 13
    2010 3 Beverages 2323

    And, the third has performance summary / report based on year-month against the selected criterions in the first segment

    Summary / Report
    Year Month Quantity
    2010 1 ???
    2010 2 ???
    2010 3 ???


    The max I've reached for the formula against Quantity is as follows:

    =SUMPRODUCT(($C$16:$C$20=D26)*($D$16:$D$20=E26),$F$16:$F$20)

    but, this doesnt consider the selection that the user has made, and as such the month-wise totals are coming up. I'm aware that I can use PivotTable with ease for this requirement, but I'd ideally prefer a non-PivotTable solution as there are many other dependencies.

    Also, if the user-selected categories can be dynamically captured as a named range - could use it for several validation moving forward.

    Thanks in advance!

    Noaman
    Attached Files Attached Files

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Multi criteria sumproduct name range query

    Try:

    =SUMPRODUCT(($C$16:$C$20=D26)*($D$16:$D$20=E26)*(LOOKUP($E$16:$E$20,$E$4:$F$9)="Yes"),$F$16:$F$20)

    The values in E4:E9 must be sorted alphabetically though.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Registered User
    Join Date
    03-15-2011
    Location
    Dubai
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Multi criteria sumproduct name range query

    Hi Dom!

    It works like a charm - thank you sooo much!!!!

    Once again - thanks for the prompt response!!!

    Ta,
    N.

  4. #4
    Registered User
    Join Date
    03-15-2011
    Location
    Dubai
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Multi criteria sumproduct name range query

    Hello again!

    Wondering if its possible with the same example to actually count the distinct values in the given column

    I'm aware that I can get distinct count (including text) by saying: =SUM(IF(FREQUENCY(IF(LEN(range:range)>0,MATCH(range:range,range:range,0),""), IF(LEN(range:range)>0,MATCH(range:range,range:range,0),""))>0,1)) as an array....

    from the formula given by Dom:

    =SUMPRODUCT(($C$16:$C$20=D26)*($D$16:$D$20=E26)*(LOOKUP($E$16:$E$20,$E$4:$F$9)="Yes"),$F$16:$F$20)

    is it possible to get distinct count similar to abovementioned so that instead of getting a sum from F16:F20 --> I actually get distinct count from J16:J20?

    Would this be possible without the use of macros?


    Thanks in advance!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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