Excel Sumif with Array

Question

I don't know if I'm not doing something right or if SUMIF can't handle the array,

what I've been trying to do is ;

SUMIFS( G4:G18,TEXTAFTER(I2," ")&" "&LEFT(I2),E4:E18&" "&F4:F18)

I've got filter to work

FILTER(G4:G18,E4:E18&" "&F4:F18=TEXTAFTER(I2," ")&" "&LEFT(I2)),
which I can then sum,

or sumproduct;

SUMPRODUCT(--(TEXTAFTER(I2," ")&" "&LEFT(I2)=E4:E18&" "&F4:F18),G4:G18)

Is it possible to use Sumifs in this situation

Answer 1

Two things to note:

1. SUMIF and SUMIFS expect ranges (not calculated arrays) in some of their arguments

see https://real-statistics.com/excel-capabilities/array-arguments-in-excel-formulas/

2. Be aware that SUMIF and SUMIFS require their arguments in a different order.

Answer 2

Hi,

the reason the SUMPRODUCT() function works is that the input into this function is an array (and not a range). In a SUMIF() function, the input is a range. E3:E8&" "&F3:F8 is an array (not a range).

Hope this clarifies.

Answer 3

Hi,

Does it work when you replace comma with semi-colon?

Answer 4

Sumproduct as shown in original example works, but leaving out any any the space that I had does not seem to make a difference, I still get the same error.

Answer 5

No, afraid not I've got the principal to work by using helpers,
but as soon as I try it in one formula it fails. As I hope this shows I've added helper range and criteria and this works.

If you can see what I'm doing wrong please let me know,

RD