Array issue with SUMIFS

[lost_in_the_sauce]

I have a multiple criteria SUMIFS function

=SUMIFS(INDEX('data1'!$J:$U,0,MATCH(Sheet1!$M$2,Key!$B$14:$B$25,0)), 'data1'!$I:$I, "actual", 'data1'!$F:$F, "Expense", 'data1'!$A:$A, $A10)

I would like to have the last criteria search for matches from 2 cells, $A10 AND $B10.
I have tried

=SUMIFS(INDEX('data1'!$J:$U,0,MATCH(Sheet1!$M$2,Key!$B$14:$B$25,0)), 'data1'!$I:$I, "actual", 'data1'!$F:$F, "Expense", 'data1'!$A:$A, $A10 OR $B10) and the result was 0.

I have tried

=SUMIFS(INDEX('data1'!$J:$U,0,MATCH(Sheet1!$M$2,Key!$B$14:$B$25,0)), 'data1'!$I:$I, "actual", 'data1'!$F:$F, "Expense", 'data1'!$A:$A, {$A10,B10})
and
=SUM(SUMIFS(INDEX('data1'!$J:$U,0,MATCH(Sheet1!$M$2,Key!$B$14:$B$25,0)), 'data1'!$I:$I, "actual", 'data1'!$F:$F, "Expense", 'data1'!$A:$A, {$A10:$B10})

only to get an error "There was a problem with this formula - Not trying to type a formula? I also tried ctrl-shift-enter but it makes the entire formula an array and still returns 0.

 

[jasonb75]

=SUM(SUMIFS(INDEX('data1'!$J:$U,0,MATCH(Sheet1!$M$2,Key!$B$14:$B$25,0)), 'data1'!$I:$I, "actual", 'data1'!$F:$F, "Expense", 'data1'!$A:$A, {$A10:$B10})

That one looks right, but you need to remove the {} from the criteria.

 

[lost_in_the_sauce]

I get values for this function with either $A10 or $B10 after removing the {} but $A10:$B10 returns a value of 0

 

[alz]

How about this?

=SUMIFS(INDEX('data1'!$J:$U,0,MATCH(Sheet1!$M$2,Key!$B$14:$B$25,0)), 'data1'!$I:$I, "actual", 'data1'!$F:$F, "Expense", 'data1'!$A:$A, $A10,'data1'!$A:$A,$B10)

 

[jasonb75]

I get values for this function with either $A10 or $B10 after removing the {} but $A10:$B10 returns a value of 0

Noting from your other thread that you don't have the dynamic array update, try array confirming it with Ctrl Shift Enter, or use SUMPRODUCT instead of SUM.

@alz that would not work, it would look for cells in column A that contain a match for both A10 and B10, given that a single cell can only be equal to one thing or the other but never both, it will always return zero.

 

[lost_in_the_sauce]

Noting from your other thread that you don't have the dynamic array update, try array confirming it with Ctrl Shift Enter, or use SUMPRODUCT instead of SUM.

@alz that would not work, it would look for cells in column A that contain a match for both A10 and B10, given that a single cell can only be equal to one thing or the other but never both, it will always return zero.

SUMPRODUCT - That did it. I should be able to expand the last row ($A10:$B10) to something like ($A10:F$10) and any empty cells shouldn't interfere as they would produce values of zero?

Alz - was returning a zero sum again

 

[jasonb75]

I should be able to expand the last row ($A10:$B10) to something like ($A10:F$10) and any empty cells shouldn't interfere as they would produce values of zero?

An empty cell in A10:F10 will be matched to any cell in Data!A:A that contains 0 (if any exist). Subsequently, a value in J:U of the data sheet will be included in the total if A:A contains 0.

Hope that makes sense.