# Using Name Arrays as Criteria in SumProduct/SumIfs

1. ## 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.

2. ## 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.

3. ## 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. ## Re: Using Name Arrays as Criteria in SumProduct/SumIfs

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

5. ## 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. ## 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. ## 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!

##### Users Browsing this Thread

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

#### 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