How to ignore blank criteria in a sumproduct?

[kenshin_pa]

I am trying to create a formula that will ignore blank cells in the criteria. The first part shows the criteria from row 1 to row 5 which is first name and last name and if they like Star Trek or Star Wars. The 2nd part shows the data starting in row 6.

The Result column is from the formula that we have been working on which is =SUMPRODUCT(--($D$6:$D$20=F3),--((($B$6:$B$20=B3)+(B3="")>0)+(($B$6:$B$20=C3)+(C3="")>0)),--((($C$6:$C$20=D3)+(D3="")>0)+(($C$6:$C$20=E3)+(E3="")>0)),$E$6:$E$20).

Can someone please help me figure out the formula for Should be column?

1/A		B	C	D	E	F	G	H	I
2		First Name 1	First Name 2	Last Name 1	Last Name 2	Star Trek or Star Wars		Result	Should be
3		Peter				Star Trek		306.00	45.00
4		Peter	Andrew	Smith		Star Trek		123.00	15.00
5			Andrew		Riker	Star Trek		317.00	53.00
6		First Name	Last Name	Star Trek or Star Wars	Amount
7		Peter	Smith	Star Trek	5.00
8		Andrew	Smith	Star Trek	10.00
9		Peter	Riker	Star Trek	11.00
10		Andrew	Riker	Star Trek	13.00
11		Peter	Riker	Star Trek	29.00
12		Andrew	Riker	Star Trek	30.00
13		Andrew	Riker	Star Trek	10.00
14		Andrew	Riker	Star Wars	15.00
15		Andrew	Riker	Star Wars	16.00
16		Andrew	Riker	Star Wars	18.00
17		Peter	Riker	Star Wars	34.00
18		Peter	Riker	Star Wars	35.00
19		Peter	Riker	Star Wars	15.00
20		Peter	Riker	Star Wars	20.00

<colgroup><col><col span="2"><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[pgc01]

Hi
Welcome to the board

Try:

=SUMPRODUCT(--($D$7:$D$20=F3),--((B3="")*(C3="")+($B$7:$B$20=B3)+($B$7:$B$20=C3)>0),--((D3="")*(E3="")+($C$7:$C$20=D3)+($C$7:$C$20=E3)>0),$E$7:$E$20)

 

[kenshin_pa]

pgc01 you are a genius among genuises!!! Your formula worked perfectly! I might have follow up questions but based on initial tests to the real file it works.

Thank you very much!

 

[pgc01]

I'm glad it helped!