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,

check names, initials and H3 for leading or trailing spaces.

Claus

Answer 4

I have done,

also I have taken the

SUMPRODUCT(--( TEXTAFTER(J3," ")&" "&LEFT(J3) = F5:F10&" "&G5:G10),H5:H10)

Construction, which works fine and removed sumproduct, and replaces with sumif,

and yes I did alter the range criteria so as to be correct order, still no good.

RD

Answer 5

Thanks, that is what I asked in the original post, was the error due to the fact I was giving it an array, whenever that was, so essentially sumif is not going to work with an array, if that's correct that's all I wanted to know, re other answer yes I know sumif / ifs have different construction, Ill mark this up as solved,

Thanks to all who took the trouble to respond.

RD