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.

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.

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

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.