Count Values In Two Columns That Does NOT Match

[Neyme]

Hello,

I would be very appreciative of Excel expert's help.

I use SUMPRODUCT formula to count the values in two columns that do not match but it doesn't work: =SUMPRODUCT(--(DataTable!A2:A150<>""),--(DataTable!A2:A150='Pivot Table'!A2:A150)). For some reason this formula counts a total number of values on "Data Table spreadsheet. Both columns are located on two different spreadsheets. The sample of data is attached.

SUMPRODUCT.xlsx

[Gatti]

Hello

Try this:

Please Login or Register  to view this content.

Don't forget to CTRL+SHIFT+Enter

[Jonmo1]

Hang on..

Are you wanting to count If
Sheet1!A2 = DataTable!A2
Sheet1!A3 = DataTable!A3
Sheet1!A4 = DataTable!A4

OR

Sheet1!A1 = ANY Value in DataTable!$A$2:$A$150
Sheet1!A2 = ANY Value in DataTable!$A$2:$A$150
Sheet1!A3 = ANY Value in DataTable!$A$2:$A$150

??

[JieJenn]

Give this array formula a try. Enter with Ctrl + Shift + Enter keys together

=SUM(IF(IFERROR(MATCH('Pivot Table'!$A$2:$A$65,DataTable!$A$2:$A$145,0),0)>0,1,0))

[Neyme]

Hi guys,

Thank you so much for your quick replies! Unfortunately, neither formula works. I attached the spreadsheet with the two of your formulas applied - entered as an array.

Pivot Table sheet contains 64 records which all are also listed in Data Table sheet. So the formula should return 80 records that are not matched.


SUM(IF(IFERROR(MATCH('Pivot Table'!$A$2:$A$65,DataTable!$A$2:$A$145,0),0)>0,1,0)) formula returns 64 records

SUM(($A$2:$A$150<>"")*1,(DataTable!$A$2:$A$150=$A$2:$A$150)*1) formula returns 69 records.

I need to return a number of records that are NOT matched in these two tables. Do you have any suggestions as to how the formula needs to be modified?

SUMPRODUCT.xlsx

[JieJenn]

Give this formula a try then. Enter with Ctrl + Shift + Enter keys together
=COUNTA(DataTable!A:A)-SUM(IF(IFERROR(MATCH('Pivot Table'!$A$2:$A$65,DataTable!$A$2:$A$145,0),0)>0,1,0))-1

[Neyme]

Hi JieJenn,

This is great! The formula works and correctly return the number of not matched records, which in this case is 80.

However, the formula doesn't account for the blank cells. Both tables on the Data Table tab and Pivot Table tab will have a different number of rows each week, which could range from 10000 to 50000.

Could you please update the formula to account empty cells if I set up this maximum range in the formula?

I tremendously appreciate your time and effort to help me!!!

[Neyme]

Hi Jonmo1,

I want to count if:

Sheet1!A1 = ANY Value in DataTable!$A$2:$A$150
Sheet1!A2 = ANY Value in DataTable!$A$2:$A$150
Sheet1!A3 = ANY Value in DataTable!$A$2:$A$150


and I need to account for blank cells because every week the pivot table and a data table will have a different number of rows. So I was thinking to set a maximum range, lets say 50000 rows.

Can you help me with this?

Thank you!

[Jonmo1]

Try this on the pivot table tab.

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A65,DataTable!A2:A145,0))))


I understand the need to accomodate fluxuating table sizes.
But try to keep those row #s as low as possible, go with the 10% rule
Come up with a reasonable number where you can say "certainly my data will never be more than xxx rows"
And add 10% to that number.

[Jonmo1]

Maybe we're confused on the goal.
The title says count those that DO NOT Match, but the formlua you attempted appears to count the ones that DO Match.

Then I asked for a clarification, and you said

Hi Jonmo1,

I want to count if:

Sheet1!A1 = ANY Value in DataTable!$A$2:$A$150
Sheet1!A2 = ANY Value in DataTable!$A$2:$A$150
Sheet1!A3 = ANY Value in DataTable!$A$2:$A$150

That reads that you want to count the ones that DO match.


If you're wanting to count the ones that DO NOT match, try

=SUMPRODUCT(--(A2:A65<>""),--(ISNA(MATCH(A2:A65,DataTable!A2:A145,0))))