+ Reply to Thread
Results 1 to 7 of 7

Using Name Arrays as Criteria in SumProduct/SumIfs

  1. #1
    Registered User
    Join Date
    07-07-2014
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    4

    Using Name Arrays as Criteria in SumProduct/SumIfs

    Hi All,

    My issue involves using multiple arrays as the criteria for summation.

    Some background info -
    ArrayOfCustomers - Defined Name of a list of customer names existing in another sheet within the same workbook
    ArrayOfQuarters - Defined Name of a list of quarters. Ex) Q1, Q2, Q3
    CurrentQuarter - Defined Name = "Q1"

    Table1 is on another sheet and contains thousands of rows with column headings: CustomerName, Quarter, ProductType, Revenue, and more.

    Here is an example of a formula that works CORRECTLY giving me what i want for one analysis point:

    Please Login or Register  to view this content.
    I modified it to make the Quarter Criteria an Array instead of the constant CurrentQuarter and that's when it stopped working on me providing only '0':

    Please Login or Register  to view this content.
    I also couldn't get it to work with just SUMPRODUCT. I tried this and it returns "#VALUES":

    Please Login or Register  to view this content.
    Or this returns "#N/A":

    Please Login or Register  to view this content.
    The end goal is to sum up all the Revenue for a given set of customers ordering Apples in a given set of Quarters. These will be variable as we move through the year so i made these variables as Defined Names for easy mass changes later on. The desired result should be larger than the first result due to the fact that it is multiple quarters including the original answer. I feel like i need a nested SumProduct in there somewhere or something, but I've been trying to think through the logic for hours! Any help would be greatly appreciated!

    Thank you!

    EDIT: SOLUTION FOUND - Thanks to the 'similar posts' function on this site i was able to find other similarly tagged issues. It turned up that the first one was indeed able to solve my problem. After searching on this site and the internet for a few hours with no results i simply posted this, saw the similar asks, and found an answer. Coming back here to post the solution:

    Please Login or Register  to view this content.
    Transposition on the second array along with a CSE (Ctrl+Shift+Enter for the uninitiated, as I was until today!) on a normal SUM function instead of SUMPRODUCT absolutely did the trick! Thanks to user 'XOR LX' for the solution in the other thread (Referenced Thread) that ended my headache.
    Last edited by stepscot; 07-15-2014 at 06:03 PM. Reason: Fix Found by more research

  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
    28,419

    Re: Using Name Arrays as Criteria in SumProduct/SumIfs

    Hi,

    These things are always easier to answer if you upload the workbook and we can see the request in context. In addition manually add some calculated results so that we know the end goal.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    07-07-2014
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    4

    Re: Using Name Arrays as Criteria in SumProduct/SumIfs

    Example.xlsx

    Thank you for the quick reply Richard. Attached is a quick workbook I threw together that is the same general idea with the same outcomes - I can't share the original.

  4. #4
    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
    28,419

    Re: Using Name Arrays as Criteria in SumProduct/SumIfs

    Surely the smartest way is to just use a Pivot Table - see attached.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-07-2014
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    4

    Re: Using Name Arrays as Criteria in SumProduct/SumIfs

    You're right that a PivotTable makes it easier/quicker to get for a one time deal but there are a whole slew of factors contributing to the need to grab this data with a formula:
    • This will be refreshed weekly and manually pivoting or doing a VBA macro isn't as desirable as I am trying to minimize the time spent on automating this
    • I have a macro that goes out to the raw data source and pulls in the raw data each week/daily depending on the refresh. I want to avoid having to "re-pivot" every week
    • The raw data pull comes in with the same customer having multiple names (it gets really ugly) so the ArrayOfCustomers are actually several arrays of all the possible variations of that given customers names. A better description is: ArrayOfCustomer1Names, ArrayOfCustomer2Names, etc.
    • This is essentially an executive dashboard being created for a simple "refresh and go" that gives me more freedom and less manual time each week.


    Thank you!

  6. #6
    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
    28,419

    Re: Using Name Arrays as Criteria in SumProduct/SumIfs

    Hi,

    I see nothing in any of your four points which would mitigate against a Pivot Table. The Pivot table cache can be made to refresh automatically whatever the data, so there's no need to do anything manually with it.

    As long as your data connection is bringing in the required data (an example of which you supplied) then I really can't see any difficulties. Seems to me all you need to do is either add a line of code at the end of your import macro that refreshes the PT, or put the refresh in the PT sheet Activate event.

  7. #7
    Registered User
    Join Date
    07-07-2014
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    4

    Re: Using Name Arrays as Criteria in SumProduct/SumIfs

    Thanks Richard. I do, in fact, already have a refresh line for the pivot table that is created based off of the data for leadership to do a drill down if desired. That already happens. The determining factor here is that the views that are needed are not able to be had via a PivotTable. I wanted to do as little VBA as possible due to the fact that I am handing this off to another user that is not a programmer. They will be able to understand simple formulas much better than code. Trying my best to make it as transferable as possible and, sadly, most are not comfortable with code. Thank you for your continued consideration!

+ 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. SUMIFS using arrays as criteria
    By Ricardo Mass in forum Excel General
    Replies: 5
    Last Post: 02-28-2014, 12:36 PM
  2. sumproduct IF two arrays match a range of criteria?
    By Paul Sword in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-10-2013, 10:06 AM
  3. SUMIFS or SUMPRODUCT Between Criteria
    By Trig79 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-09-2013, 09:52 AM
  4. SUMPRODUCT from using Criteria Arrays
    By quinceag in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-12-2013, 06:30 PM
  5. [SOLVED] Sumifs or sumproduct with complex criteria
    By Alexander_Golinsky in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-08-2013, 03:03 AM

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