sum product if three fields are true

Heres a Multi criteria sum product Statement, I have an error log and a production sheet.
What I want is if the error log reports an error, I want it to return the error to the production log for the correct date to the production sheet matching the Line and Zones, So basically it would be count if, the Lines are = and the Zones are = and the Dates are =.

Each Row in Sheet 2 is one error, so if Columns "Date" & "Line" & "Zone" in sheet Errors = Columns "Date" & "Line" & "Zone" in sheet Production, then it should count each item and add them in Column H in the Production Sheet. I hope this makes sense, all I want is the total amount of errors for each zone and line to automatically calculate, based on what is inputted into the Error Sheet.

I am trying to use this code, but it seems like it is only working in the first Zone on each Line (probably because the Line cells are merged), any ideas how to fix this without renaming the Line or hiding a row with the names in each cell???

{=SUM((Errors!$A:$A=Production!$B6)*(Errors!$B:$B=Production!C$2)*(Errors!$I:$I=Production!C$3))}

For illustrative purposes I have included an attachment, with both sheets and a sample of what it should look like, also in the production sheet I highlighted where values should be for a better visual...

what verion of excel will you be using?

it will be going to 2003, but would love to know how to do it in 2010 also

Given the size of the matrix - pre XL2007 you should really add a key to Errors sheet, eg:

Errors!L2:
="@"&A2&"@"&B2&"@"&I2&"@"
copied down for all rows

You can then use COUNTIF which is efficient whereas your alternatives are not.

Production!C6
=COUNTIF(Errors!$L:$L,"@"&$B6&"@"&LOOKUP(REPT("Z",255),$C$2:C$2)&"@"&C$3&"@")
applied to matrices

In XL2010 (as in XL2007) you could use COUNTIFS to negate need for Key

Without the key you are left with Arrays and/or SUMPRODUCT which are inefficient and should not be used in large quantity (as would be the case here) - assuming performance is of interest.

Thanks for your assistance day92, appreciate it.

Originally Posted by DonkeyOte

Without the key you are left with Arrays and/or SUMPRODUCT which are inefficient and should not be used in large quantity (as would be the case here) - assuming performance is of interest.

You really are a smart cookie, I aspire to pick up this knowledge...performance is always of interest and I am afraid that is one aspect that I do not fully understand...volatile excel functions...someday I'm hoping I will

Originally Posted by 00Able

I aspire to pick up this knowledge

I'd draw your attention to the below:

Originally Posted by Rita Mae Brown

Good judgment comes from experience, and often experience comes from bad judgment

That's how most people learn that SUMPRODUCTs & Arrays are bad news when used in large quantity and/or with large precedent ranges and/or in Volatile manner.

Originally Posted by 00Able

performance is always of interest and I am afraid that is one aspect that I do not fully understand...volatile excel functions...someday I'm hoping I will

Check out the Volatility link in my sig. - and in fact read through the whole site in full - it's the best resource on Excel performance & optimisation re: calculation engine etc...
(the author Charles Williams is an MS Excel MVP and has written white papers for them on the same subject - covering a lot of the same material)