Sumproduct help

[LOUBOYBEAR]

Hi all, I am wanting to create a sumproduct formula looking at three columns. One of these colums has 3 possible criteria I want to pull out.
I have tried using the OR function for the range with 3 possible codes but it doesnt work.

I need to count column L if column F = "O" and
Column J = "RS" OR "DE" OR "TE"

I have tried
=SUMPRODUCT($F$3:$F$996="O")*(OR($J$3:$J$996="rs",'$J$3:$J$996="de",$J$3:$J$996="te"))*($L$3:$L$996))

any suugestions appreciated!
thanks.

 

[GTO]

Are you trying to count how many rows make all the criteria, or multiply any part?

 

[cgcamal]

Hi LOUBOYBEAR,

Try with this array formula:

Excel Workbook
	F	G	H	I	J	K	L	M
1	R				t		1	7
2	t				er		2
3	O				RS		4
4	O				DE		1
5	L				tr		5
6	O				lo		1
7	O				TE		2
...

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

Regards.

 

[LOUBOYBEAR]

Yes, column L has an 1 in each row. Any entries that are TRUE in columns J and F are multiplied by 1 (from column L)

 

[Marcelo Branco]

Maybe this

=SUMPRODUCT(($F$3:$F$996="O")*(($J$3:$J$996="rs")+($J$3:$J$996="de")+($J$3:$J$996="te"))*$L$3:$L$996)

HTH

M.

 

[GTO]

Your formula seemed to work for me, other than a typo I think:
=SUMPRODUCT(($F$3:$F$996="O")*(OR($J$3:$J$996="rs",$J$3:$J$996="de",$J$3:$J$996="te"))*($L$3:L$996))

I note that if L col has numbers other than 1, these are reflected in the return. If you only want the row counted if L Col has a 1, then maybe:

=SUMPRODUCT(--($F$3:$F$996="O")*(OR($J$3:$J$996="rs",$J$3:$J$996="de",$J$3:$J$996="te")),--($L$3:L$996=1))

 

[LOUBOYBEAR]

Thanks everyone for your help. got it sorted now!