sumifs/sumproduct formula returns error or zero

Hello All

I'm trying to sum all received quantities (from column E) with few criteria
* Purchase Order (column G Sheet1) only if starts with "47xxx)
* valuation type (column F Sheet1) = NEW
* movement type (column J Sheet1) = GR goods receipt

I have tried sumproduct = formula in column D (sheet2) and sumifs = formula in column E (sheet2) however ... first solution shows N/A and second "0"

where is the issue?

any support please

thanks

I changed you SP formula in D2 to this:

=SUMPRODUCT((LEFT(Sheet1!$D$2:$D$64674, 2)="47")*(Sheet1!$A$2:$A$64674=Sheet2!B1)*(Sheet1!$F$2:$F$64674=Sheet1!$F$2)*(Sheet1!$C$2:$C$64674="new"))

by removing the asterisk from the "47*" and by changing the range in the second condition term from $A$1 to $A$2 to make all the ranges the same size. This got rid of the errors and now returns 0 like the SUMIFS formula.

Are you sure that there are any records which match those criteria?

Hope this helps.

Pete

Hiya
works perfectly
thank you