Sumproduct #Value! Error due to Text

You can use an array formula

=SUM(IF(C7:C16=C2,IF(ISNUMBER(D7:F16),D7:F16)))

which needs to be confirmed with CTRL+SHIFT+ENTER

Originally Posted by Carim

Hi,

Thanks for your file ...
Found out you are not in a "sumproduct-type" configuration ...which is when you are dealing with several conditions ...
see attached ...

Thank-you so much Carim and Daddylonglegs,
You have helped me a lot.

Thanks again,
Ricky

Originally Posted by Carim

Hi,

Thanks for your file ...
Found out you are not in a "sumproduct-type" configuration ...which is when you are dealing with several conditions ...
see attached ...

Hi again Carim,
I've been using your formula and it works well. Just curious, the formula refers to row number "+5". Why the number 5?

I'd like to use this formula for another section but it may not start from 2002 to 2007 anymore. It may start from 1999 and go as far as 2007 instead. Would I have to keep updating the "+5" number or is there a better way so that the formula could pick out the proper row automatically?

Thanks again,
Ricky

Originally Posted by Carim

Hi,

In order to get the exact row number, given the fact you are storing your different years from C6 or row 6 ... the five first rows are missing in the total to get the exact row number ...

Oh I see,
That's the empty rows between the reference cell C2 and the first row of the data line ie. year 2002.

Got it.

Thanks for shining the light.
Ricky