# Counting Unique differences between two worksheets

#### D-Spark

##### Board Regular
I have the following formula

=SUMPRODUCT(--(LEFT(Apr08WFA!\$A\$1:\$A\$4000,2)="zz"),--(TEXT(Apr08WFA!\$F\$1:\$F\$4000,"mmmyyyy")=TEXT(B6,"mmmyyyy")))+SUMPRODUCT(--(LEFT(Apr08WFA!\$A\$1:\$A\$4000,2)="zz"),--(TEXT(Apr08WFA!\$F\$1:\$F\$4000,"mmmyyyy")=TEXT(B7,"mmmyyyy")))

this counts the number of entries on sheet Apr08WFA when value in range F1 to F4000 is either apr2008 (cellB6) or mar2008 (cellB7).

this works in principle but does not catch all.

I want to amend the formula to catch new entries appearing on a sheet called May08WFA that were not on Apr08WFA but with the same condition of "zz".

So data on May08WFA will be compared against Apr08WFA and new entries counted.

challenge I know but am confident one of your high level users can assist.

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### DonkeyOte

##### MrExcel MVP
assuming that May08WFA range is exactly the same size and position as Apr08WFA the following should work:

=SUMPRODUCT(--(LEFT(Apr08WFA!\$A\$1:\$A\$4000,2)="zz"),--(TEXT(Apr08WFA!\$F\$1:\$F\$4000,"mmmyyyy")=TEXT(B6,"mmmyyyy")))+SUMPRODUCT(--(LEFT(Apr08WFA!\$A\$1:\$A\$4000,2)="zz"),--(TEXT(Apr08WFA!\$F\$1:\$F\$4000,"mmmyyyy")=TEXT(B7,"mmmyyyy")))+SUMPRODUCT(--(LEFT(May08WFA!\$A\$1:\$A\$4000,2)="zz"),--(TEXT(May08WFA!\$F\$1:\$F\$4000,"mmmyyyy")=TEXT(B6,"mmmyyyy")),--(May08WFA!\$A\$1:\$A\$4000<>Apr08WFA!\$A\$1:\$A\$4000))+SUMPRODUCT(--(LEFT(May08WFA!\$A\$1:\$A\$4000,2)="zz"),--(TEXT(May08WFA!\$F\$1:\$F\$4000,"mmmyyyy")=TEXT(B7,"mmmyyyy")),--(May08WFA!\$A\$1:\$A\$4000<>Apr08WFA!\$A\$1:\$A\$4000))

However, I'm pretty sure this could be streamlined by one of the formula gurus on here...

Are the dates (1 & 2) always sequential (Mar08, Apr08) or could you have Apr07 and Mar08 for ex ?

#### DonkeyOte

##### MrExcel MVP
I think this works a little better ?

So rather than having SUMPRODUCT1 + SUMPRODUCT2 based on different mmmyyyy criteria the below compresses that into 1 SUMPRODUCT and using a combination of ISERROR check based on mmmyyyy against CONCATENATION of the 2 critera in one go -- then this is repeated for the additional unique items on the 2nd sheet that are not present on the first.

So instead of 4 SUMPRODUCTS being aggregated you have just 2.

=
SUMPRODUCT(--(LEFT(Apr08WFA!\$A\$1:\$A\$4000,2)="zz"),--(ISERROR(FIND(TEXT(Apr08WFA!\$F\$1:\$F\$4000,"mmmyyyy"),CONCATENATE(TEXT(\$B\$6,"mmmyyyy"),",",TEXT(\$B\$7,"mmmyyyy"))))=FALSE))
+
SUMPRODUCT(--(LEFT(May08WFA!\$A\$1:\$A\$4000,2)="zz"),--(ISERROR(FIND(TEXT(May08WFA!\$F\$1:\$F\$4000,"mmmyyyy"),CONCATENATE(TEXT(\$B\$6,"mmmyyyy"),",",TEXT(\$B\$7,"mmmyyyy"))))=FALSE),--(May08WFA!\$A\$1:\$A4000<>Apr08WFA!\$A\$1:\$A\$4000))

Hope that works for you, let me know.

Last edited:

#### D-Spark

##### Board Regular
Thanks Im I able to use the reply you gave but not refer to B6 and B7.
these are dates whereby I only want the difference between the two sheets as some item entries might be new on the latest sheet "May08" in this instance but F range might show a date way in the past but which has only now been added to the log

Last edited:

#### DonkeyOte

##### MrExcel MVP
Sorry, I don't really understand your note.

If you're saying you want to count instances on sheet1 where month = either date specified in B6 / B7 and text begins "zz" and you want to add to that instances on sheet2 where text begins "zz", month = either date specified in B6/B7 BUT where the text does not exist on sheet1 the latter formula should work.

Thanks,

#### D-Spark

##### Board Regular
I will try to explain:

I want to compare range B1:B4000 on "May08WFA", for all entries in this range where the first two characters = zz.

These values will then be compared agaisnt agaisnt range B1:B4000 on "Apr08WFA". To identify new zz entries in May08WFA that werent on Apr08WFA

the result will be to count new "zz" items on "May08WFA" that were not present in range B1:B4000 on "Apro08WFA"

#### DonkeyOte

##### MrExcel MVP
regardless of month ?

#### D-Spark

##### Board Regular
Yes regardless of month

#### D-Spark

##### Board Regular
Yes regardless of month

Maybe this problem is bigger than the both of us ! Then again a problem shared is a problem halved

Last edited:

#### DonkeyOte

##### MrExcel MVP

If I get your aim correctly I believe the following would work for you, no ?

=
SUMPRODUCT(--(LEFT(APR08WFA!\$A\$1:\$A\$4000,2)="zz"),--(ISERROR(FIND(TEXT(APR08WFA!\$F\$1:\$F\$4000,"mmmyyyy"),CONCATENATE(TEXT(\$B\$6,"mmmyyyy"),",",TEXT(\$B\$7,"mmmyyyy"))))=FALSE))
+
SUMPRODUCT(--(LEFT(MAY08WFA!\$A\$1:\$A\$4000,2)="zz"),--(ISERROR(MATCH(MAY08WFA!\$A\$1:\$A\$4000,APR08WFA!\$A\$1:\$A\$4000,0)=TRUE)))

works off the assumption that re: the additional MAY08WFA items you're only interested in zz* strings that do not appear on APR08WFA regardless of other criteria (date etc...)

Replies
2
Views
78
Replies
14
Views
2K
Replies
1
Views
407
Replies
6
Views
518
Replies
3
Views
344

1,191,669
Messages
5,987,949
Members
440,121
Latest member
eravella

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back