Array issue with SUMIFS

lost_in_the_sauce

New Member
Joined
Jan 18, 2021
Messages
26
Office Version
  1. 365
Platform
  1. 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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,453
Office Version
  1. 365
Platform
  1. Windows
=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

New Member
Joined
Jan 18, 2021
Messages
26
Office Version
  1. 365
Platform
  1. Windows
I get values for this function with either $A10 or $B10 after removing the {} but $A10:$B10 returns a value of 0
 

alz

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,453
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 
  • Like
Reactions: alz
Solution

lost_in_the_sauce

New Member
Joined
Jan 18, 2021
Messages
26
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,453
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,584
Messages
5,625,644
Members
416,124
Latest member
DeMoNloK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top