Sumproduct #Value! Error due to Text

Hello,
I'm using this sumproduct and it works until a cell has text.
=SUMPRODUCT(($C7:$C16=$C$2)*($D7:F16))

I've tried =SUMPRODUCT(--($C7:$C16=$C$2),($D7:F16)) but got the #Value! error because of the D7:F16 range. If I changed it to become F7:F16, then it works. But...my original formula adds all values giving me a YTD result. If I changed the range to be only F7:F16, then all I get is just that column's data. I have January to December's data across the columns from D to F. Any ideas on keeping my D to F range? I've tried Transpose but it didn't work either.

Thanks in advance,
Ricky

Originally Posted by Carim

Hi,

Why don't you upload a zipped copy of your worksheet, for a precise answer ...?

Hi Carim,
Here's my file. I appreciate your help.

Thanks,
Ricky

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