#VALUE - no reason ?

[Steve_in_Kent]

I have a table with a while bunch of sumproduct formulae

eg:-

=SUMPRODUCT((Register!$F$4:$F$9999=$B4)*(Register!$AD$4:$AD$9999="G")*(Register!$Y$4:$Y$9999<>"Closed"))

This all worked fine.. somehow, in the main table someone has done something to make every result a #VALUE

Each of these columns is set to General. type.

it WAS fine, but somehow, someone has changed something on the 'Register' Tab, to make loads of these formulas go to #VALUE. By removing parts of the formula above ,it looks like its the AD column.
Which is just a letter 'G' 'A' or 'R'

[HansV]

If any of the cells in AD4:AD9999 contains #VALUE!, the SUMPRODUCT formula will return #VALUE! too.

[HansV]

If you're using Excel 2007 or later, you can use

=COUNTIFS(Register!$F$4:$F$9999, $B4, Register!$AD$4:$AD$9999, "G", Register!$Y$4:$Y$9999, "<>Closed")

This should work even if there are error values in any of the columns you refer to.