Hi I have the following formula that I'm trying to debug:
=SUMIFS(INDEX(Sheet6!$B$3#,,2),INDEX(Sheet6!$B$3#,,3),[@Date])
The formula behaves more like a lookup, returning only the first value it finds, rather than summing all the values that meet the criteria. I suspect it is something to do with trying to sum arrays rather than ranges, but I'm finding conflicting information on this.
I've tried other potential solutions like using sumproduct, but I'm finding errors in that sumproduct is strugging to retrive the date from the array.
I'm not exactly sure where you were going with the INDEX, but I've encountered a similar situation, tried similar approaches, and eventually reached a simple solution:
Let's assume you want to sum all the cells in the range B1:D100, based on a filter operated on column A. For instance, you only want to sum cells from rows where the value in Col A is 5.
Enter this formula in the result cell: =SUM(IF($A$1:$A$100=5,$B$1:$D$1,0)) '5 is the criteria. Replace as needed.
BUT DON'T HIT ENTER. Instead, hold Ctrl and Shift (together) and hit Enter.
The resulting formula in the cell will appear as {=SUM(IF($J$8:$J$236=$G2,$M$8:$P$236,0))}
(Note that excel added curly braces. This is an array formula. Do not enter curly braces manually, or the formula won't work. Do it with Ctrl-shift-enter as explained above).
And Voilà, problem solved.