lost_in_the_sauce
Board Regular
- Joined
- Jan 18, 2021
- Messages
- 128
- Office Version
- 365
- Platform
- Windows
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.
=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.