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 Richard,

try:

=SUMPRODUCT((E4:E16&F4:F16=TEXTAFTER($I$2," ")&LEFT($I$2))*G4:G16)

or

=SUMIFS(G4:G16,E4:E16,TEXTAFTER($I$2," "),F4:F16,LEFT(I2))

Claus

Answer 4

I've used both it made no difference, just 'problem with formuala' pop up.