Excel - Sumif/Sumproduct Not Working; Criteria contains partial text

[AlmostBeginner19]

I need your help in getting the right formula for this.

In column B, I have a list of items called Support Type.
There're just 2 types, 535 and 532. These are taken over from a larger table with a formula.

Column D lists the Employee Names. Also taken over from another table with a formula.
The following columns, E to L, are numbers, again taken from another table with a formula.

I want get the sum/total of columns E to L, whenever an employee name, matches with a specific support type.
So for example, if employees 1, 3, and 5 falls under support 535, I want to get the sum of their values in columns E to L.
And then get the sum of columns E to L for support 532 employees.

I've tried a bunch (and I mean a bunch) of formulas with Sumif, and then realized Sumif couldn't handle arrays, so I tried Sumproduct formulas, but I'm instead getting a value error.

This is the last formula that I tried =SUMPRODUCT(--(SEARCH("535",B3:B29)),--(E3:L29)).

And I'm getting a value error.

Please help

 

[Little_Clubber]

You're right, SUMIF doesn't work with an array and will sum only the first column in the selected sum_range.
That said, it can use an additional =SUM formula.

What I'd suggest is this:

In 'M3', add a new formula, =SUM(E3:L3)
Fill down to Row 29

The formula you then want is: =SUMIF(B:B,535,M:M)

 

[AlmostBeginner19]

Oh my god it worked. Dooooooooooooooooood!
Thank you so much! I've been working on this for half my shift!

You're right, SUMIF doesn't work with an array and will sum only the first column in the selected sum_range.
That said, it can use an additional =SUM formula.

What I'd suggest is this:

In 'M3', add a new formula, =SUM(E3:L3)
Fill down to Row 29

The formula you then want is: =SUMIF(B:B,535,M:M)