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?

[TABLE="width: 801"]
<colgroup><col><col span="2"><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]1/A[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]First Name 1[/TD]
[TD]First Name 2[/TD]
[TD]Last Name 1[/TD]
[TD]Last Name 2[/TD]
[TD]Star Trek or Star Wars[/TD]
[TD] [/TD]
[TD]Result[/TD]
[TD]Should be[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Star Trek[/TD]
[TD][/TD]
[TD] 306.00[/TD]
[TD] 45.00[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD]Andrew[/TD]
[TD]Smith[/TD]
[TD][/TD]
[TD]Star Trek[/TD]
[TD][/TD]
[TD] 123.00[/TD]
[TD] 15.00[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Andrew[/TD]
[TD][/TD]
[TD]Riker[/TD]
[TD]Star Trek[/TD]
[TD][/TD]
[TD] 317.00[/TD]
[TD] 53.00[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Star Trek or Star Wars[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD]Smith[/TD]
[TD]Star Trek[/TD]
[TD] 5.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]Andrew[/TD]
[TD]Smith[/TD]
[TD]Star Trek[/TD]
[TD] 10.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD]Riker[/TD]
[TD]Star Trek[/TD]
[TD] 11.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD]Andrew[/TD]
[TD]Riker[/TD]
[TD]Star Trek[/TD]
[TD] 13.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD]Riker[/TD]
[TD]Star Trek[/TD]
[TD] 29.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD]Andrew[/TD]
[TD]Riker[/TD]
[TD]Star Trek[/TD]
[TD] 30.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD]Andrew[/TD]
[TD]Riker[/TD]
[TD]Star Trek[/TD]
[TD] 10.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD]Andrew[/TD]
[TD]Riker[/TD]
[TD]Star Wars[/TD]
[TD] 15.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD]Andrew[/TD]
[TD]Riker[/TD]
[TD]Star Wars[/TD]
[TD] 16.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD]Andrew[/TD]
[TD]Riker[/TD]
[TD]Star Wars[/TD]
[TD] 18.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD]Riker[/TD]
[TD]Star Wars[/TD]
[TD] 34.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD]Riker[/TD]
[TD]Star Wars[/TD]
[TD] 35.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD]Riker[/TD]
[TD]Star Wars[/TD]
[TD] 15.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD]Riker[/TD]
[TD]Star Wars[/TD]
[TD] 20.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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!