Sumproduct and ignoring blank cells

[hannahmc8]

HI I have the following formulas which do the same thing:
=SUMPRODUCT(--($H$21:$H$22=$C$9),--($C$21:$C$22=$C$11),$K$21:$K$22)

=SUMPRODUCT(--($C$9=$H$21:$H$22),($C$11=$C$21:$C$22)*($K$21:$K$22))

The fields all correspond with cells which have values and it is working perfectly until I increase the column sizes from H22 to H23 and beyond, or C22 to C23..., or K22 to K23...
The cells that are now encompassed have blanks within the range.
How can I get this to work ignoring the blank cell values?

The blank cells have corresponding validation lists attached to them - will that make a difference and how do I remedy if that is the case?
Many thanks
Hannah

 

[Andrew Poulsom]

In what way do blank cells affect the results of your formulas? They had no effect for me.

 

[hannahmc8]

Hi Andrew,
It is bizarre, it refers a VALUE error reference as soon as any of the columns are extended to include a blank cell. If I use the same formula on a small table with blanks it works but not with my larger version
Hannah

 

[Andrew Poulsom]

Are you sure that all your range references are the same size when you expand them?