Count cells from range that differ from another range based on multiple criteria

I am trying to return a count of values any given date (Testing!D$2), only if the first 8 characters from a column on one sheet differ from the first 8 characters on another column on another sheet. Additionally it must not county any with the value "deferred" in a specific column. Essentially, I have a row of dates and need to tally only values which are unique to the second sheet (domestic 2) and not already present on the first sheet (domestic 1). My definition of unique is having first 8 characters (left function) that are not already present on the first sheet. I know the not function cannot be used in countifs scenario. I have have used the following formulas but found no success:

=SUMPRODUCT(--('Domestic 2'!$J$2:$J$1000=Testing!D$2),--((LEFT('Domestic 2'!$A$2:$A$1000,8))<>(LEFT('Domestic 1'!$A$2:$A$1000,8))))
{=SUM(((LEFT('Domestic 2'!$A$2:$A$1000,8)<>LEFT('Domestic 1'!$A$2:$A$1000,8)))*('Domestic 2'!$J$2:$J$1000=Testing!D$2))}

Additionally I do not understand why this function does not work or how I can make it work. I know the issue is in the last range/criteria:
=COUNTIFS('Domestic 2'!$J$2:$J$1000,Testing!D$2,'Domestic 2'!$C$2:$C$1000,"<>Deferred",(LEFT('Domestic 2'!$A$2:$A$1000,8),"<>"&LEFT('Domestic 1'!$A$2:$A$1000,8)))

Really appreciate any help!
Blake

Originally Posted by dredwolf

Maybe if you could upload a sample WB with data approximating your real sheet, and some examples in it showing what you expect, we can figure something out for you

P.S.
please show what your formulas are giving as well...sometimes it as simple as using ABSOLUTE referencing instead of RELATIVE referencing

Hope this helps

Thanks for the respose. I have attached a workbook that mocks the scenario, my desired outcome along with 3 attempts. Using F9 I can tell that the true/false they are delivering is not what I need. For example, I need it to check if the first 7 characters of C2 are present anywhere in A2:A12, not just in A2.

Please take a look. I am surprised I am so stumped on this one.

Thanks,
BlakeCountExcelHelp.xlsxCountExcelHelp.xlsx

Originally Posted by dredwolf

Maybe if you could upload a sample WB with data approximating your real sheet, and some examples in it showing what you expect, we can figure something out for you

P.S.
please show what your formulas are giving as well...sometimes it as simple as using ABSOLUTE referencing instead of RELATIVE referencing

Hope this helps

Thanks for the respose. I have attached a workbook that mocks the scenario, my desired outcome along with 3 attempts. Using F9 I can tell that the true/false they are delivering is not what I need. For example, I need it to check if the first 7 characters of C2 are present anywhere in A2:A12, not just in A2.
CountExcelHelp.xlsx
Please take a look. I am surprised I am so stumped on this one.

Thanks,
Blake

Thanks so much icestationzbra! It all came together once I realized I was missing the MATCH. I used the following in my sheet but may switch over to your INDEX method to avoid using Array formula.

Please Login or Register  to view this content.