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:
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':
I also couldn't get it to work with just SUMPRODUCT. I tried this and it returns "#VALUES":
Or this returns "#N/A":
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:
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.
Bookmarks