Value Error with SumProduct formula

Aspose.Cells Product Family
1

Hi,

There is my formula :

=SUMPRODUCT((RIGHT(Data!B2:B1317, 8)="/01/2006")*ISBLANK(Data!H2:H1317))

It works well in Excel, but I can't add it at runtime with Aspose.Cells v4.2.0.0.

Once the spreadsheet opens, the formula returns a value error.

Thanks for your help.

Tom.

2

Hi Tom,

Please try this attached fix.

3

I'm sorry, the issue is not resolved with this fix.

I'm using others complex formulas such as :

=SUMPRODUCT(1*(TEXT(Données!B2:B1317;"mm/aaaa")="12/2006")*(Données!V2:V1317=B6)*(Données!J2:J1317=C3))

And i wonder whether the issue only occur with complex formulas because i have done a successful test with something like:

=SUMPRODUCT(1*(A1:A7="A"))

Tom.

4

Hi,

We tested and found the #VALUE! error related the formula (

=SUMPRODUCT(1*(TEXT(Données!B2:B1317,"mm/aaaa")="12/2006")*(Données!V2:V1317=B6)*(Données!J2:J1317=C3))

) in the output file.

We will figure our your issue soon.

Thank you.

5

Please try this attached version.

6

Hi Laurence,

All is Ok.

Thank you for your quick reply.

Tom.

7

Hi,

It's me again.

I have another value error with formulas such as :

=SUMPRODUCT((TEXT(A4:A16;"mm/aaaa")="06/2006")*(J4:J16))

Thanks for your help.

Tom.

8

Hi,

Thanks for pointing it out.

We will resolve it soon.

Thank you.

9

Please try this dll.

10

Thank you, the formula i have posted works well now. But it was just an example.

If you tried to reference the A and J column to another sheet, the value error would come back.

=SUMPRODUCT((TEXT(Data!A4:A16;"mm/aaaa")="06/2006")*(Data!J4:J16))

Tom.

11

Hi Tom,

Thanks, we tested and got "#VALUE!". we will figure out your issue soon.

Thank you.

12

Hi Tom,

Please try this attached fix.

13

Thank you, All is OK.

Tom.

14

Thanks

I will try it