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

I still can't get it to wok;

What am I doing differently to yours which obviously does,

I've done a straightforward boolean, range to criteria which give a correct true , false,

but as soon as I wrap in the sumif it don't work?