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

AlmostBeginner19

New Member
Joined
Oct 30, 2019
Messages
11
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 :(
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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)
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top