Help to correct formula

[nhnn1986]

Hi all

I have two formula but one have result, one have result = 0. I can;t find out the mistake of wrong formula:

formula have result =+SUMPRODUCT((('ky1'!$E$2:$E$9999="0101")*(LEFT('ky1'!$A$2:$A$9999;6)="G03654"))*('ky1'!$F$2:$F$9999:'ky1'!$G$2:$G$9999))

formula result = 0: =+SUMPRODUCT((('ky3'!$D$2:$D$9999="0101")*(LEFT('ky3'!$A$2:$A$9999;6)="G01014"))*('ky3'!$E$2:$E$9999:'ky3'!$F$2:$F$9999))

Please help me, thanks./.

 

[aqeelnokia99]

in first formula u try to find value G03654 and in second u find G01014 so your formula is ok check your data my not have start value G01014

 

[nhnn1986]

Hi aqe

First formula I sum all value in sheet(ky1) range F2:G9999 with two condition: first conditions is "0101" in E2:E9999, second condition is have "G03654" in 6 first letter

Second formula I sum all value in sheet(ky3) range E2:F9999 with two condition: first conditions is "0101" in D2:D9999, second condition is have "G01014" in 6 first letter

The result is: First formula have correct result but Second formula equal 0, this is wrong result in formula 2

 

[nhnn1986]

I 've found the way to replace the second formula sumproduct by sumifs, the result is right:

=+SUMIFS('ky3'!$E$2:$E$9999;'ky3'!$D$2:$D$9999;"0101";'ky3'!$A$2:$A$9999;"G01014*")+SUMIFS('ky3'!$F$2:$F$9999;'ky3'!$D$2:$D$9999;"0101";'ky3'!$A$2:$A$9999;"G01014*")

Many thanks alls had read this topic./.