Sumproduct including text

[tajumaru]

Hi,

I am new to this forum and would to post a question regarding sumproduct. I was able to do this Open Office Calc but unable to replicate the results in Excel 2007.

This is the formula: =SUMPRODUCT(1+($B$13:$B$39=$J$12),1*($C$13:$C$39=$I14),1*($G$13:$G$39)) wherein B13:B39 are dates, C13:C39 are text (categories) and G13:G39 are numbers and J12 and I14 are the criteria . Using this formula in Open Office Calc returns the result as numbers while in Excel 2007, this same formula always returns a result of zero "0". I read somewhere that Excel treats text as zero and therefore this formula will result in zero. I am not sure about that as I am not an expert in Excel.

Is there a way for me to circumvent Excel in the way text are treated and make it return the correct results like in Open Office Calc. Please help as I really need this resolved. Any help or suggestions will be greatly appreciated. Thanks in advance and more power to your site.

 

[tajumaru]

Thank you all for your support and help regarding this issue. I am sorry I was not able to come back here right away due to an out of town business trip.

I tried the formula given by xenou and it works. However, when I add additional data (e.g. the following day, new set of data), the formula returns zero. Anybody knows why this is happening? Please forgive my ignorance regarding this. Hoping for your expert help and understanding.

Thanks.