Need help with a formula to caculate multiple numbers in a sheet per name

[Stryfe]

It's tough to describe in a title and I did a quick mock up sheet to show what I need.

Column B shows the names of the sales people
Column C is the date of sale
Column D is which red part they sold (Part A or Part B)
Column E is the points awarded depending on a Red A or Red B
the remaining columns F thru I follow the same pattern with different colored parts.

again, this is widgets or whatever.

Cell S3 should calculate all the points Bob has accumulated in columns E,G and I while ignoring errors, blanks...everything but those numbers.
Cell T3 for Tom and Cell U3 for Mark.

I've tried several different ways but even when I get it to work for the first line i have trouble if the sheet was to go past that. I've included a sample sheet. Any help is appreciated.

[alansidman]

So long as you don't have #na in any of the cells or #value, then with these numbers

v	B	C	D	E	F	G	H	I
2
3	Name	Date	Red Part	Red pts	Blue Part	Blue pts	Green Part	Green pts
4	Bob	1/1/2019	A	10	B	20	A	15
5	Tom	4/5/2019	B	5	A	2	A	15
6	Bob	6/6/2019	B	5	A	30	A	15
7	Mark	6/7/2019	A	10	A	30	B	25

and this formula in S3 and copied over to T3 and U3 =SUMPRODUCT(($B$4:$B$7=S2)*(($E$4:$E$7)+($G$4:$G$7)+($I$4:$I$7)))

You will get the following:

v	S	T	U
1	Total pts.
2	Bob	Tom	Mark
3	95	22	65
4

[modytrane]

See attached file.
I used formula suggested by Alan.
Modified your formula in cells E4:E7, G4:G7 and I4:I7.
Typical formula looks like:

This eliminates #na and other errors.

Let us know if this works for you.

[Stryfe]

Thank you both alansidman and modytrane!

Great help and I learned some new methods I am sure I will be using in the future. My sheet is almost ready yo go live. My only snag will be one of my point cells was a vlookup so I will have to figure out a way to get that not to show an error or I will have to use modytrane's method and just list out each point in the formula.

Thank you both again

[Glenn Kennedy]

=IFERROR(formula,"")

will leave a blank. Alternatively, you can use an array formula, with the errors still there:

=SUMPRODUCT(IFERROR(($B$4:$B$12=S2)*($D$4:$I$12),""))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...