# Counting formula: counting new entries between sheets and numbers deleted

#### D-Spark

##### Board Regular
Hi all,
I have a workbook with three Worksheets

breakdown
May08WFA
Apr08WFA

On "breakdown" I want to acheive a formula in cell O7 to count the number of occurances where a value in range(A1:A2000) on "May08WFA" is different to value in range(A1:A2000) on "Apr08WFA"

What this will show is a number reflecting a count of new entries (account names), between dataset Apr08WFA and May08WFA.

Also if range O8 also could count number of occurnaces where there is a MATCH on account name. This will allow me to see not only the number of new account names but also the number of account names, consistance between the two datasets.

I have experimented with a host of MATCH formula and nothing appears to work

If anyone could provide a formula for both O7 and O8 that would relieve alot of my problems and assist my knowledge of how to acheive this in future.

thanks

darren

Last edited:

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### thorpyuk

##### Well-known Member
Re: Counting formula: New entries between sheets and number deleted

Are you talking about an exact match in the range? EG, the range should be in a specified order, with whatever doesn't match being counted?

This formula will count these occurances:

=SUM(IF(Sheet1!A1:A2000<>Sheet2!A1:A2000,1,0))

(array forumla, so confirm with Ctrl, Alt & Enter)

#### D-Spark

##### Board Regular
Thanks have tried this and does return value expected:
think this is due to it needing to MATCH the value from one sheet against the other.

Eg:

if one Sheet "May08WFA" range(A2)="JohnS
then if value range(A6) on Sheet "April08WFA" ="JohnS"

the expected value return would be 1 as there is a MATCH in the value "JohnS" between the two worksheet ranges.

This would be shown as 1 in the formula counting MATCHs

if one Sheet "May08WFA" range(A2)="JohnS
then if vlaue range(A2:A2000) on Sheet "April08WFA does not have anywhere in this range the value "JohnS"

the expected value "counting new additions" would be 1 as there is no MATCH in the value "JohnS" between the two worksheet ranges.

#### thorpyuk

##### Well-known Member
Ok, how about a very simple sumproduct:

=SUMPRODUCT(--(Sheet1!A2:A2000=Sheet2!A2:A2000))

This will count up all matching values.... although that said, are they unique?

#### D-Spark

##### Board Regular
I tried this and a figure of 35 was shown:

this due to the first 35 row entries being the same on each sheet

after this a new entry appeared on May08WFA ("A35") which is different to the value in the other data sheet. therefore the count appears to stop.

Manually checked and entry ("A36") on May08WFA and it appears on ("A39") on Apr08WFA

Therefore the count should match this:

Like I said it would appear that the formula is checking the value in the same cell across the two sheet.

However the same value to be (MATCHED) may appear anywhere on range on comparison sheet. (May08WFA in this instance)

So i:
If ("A36") value on May08WFA appears on cell ("A36") the this would be a MATCH and 1 shown as a count

If ("A36") value on May08WFA appears on cell ("A34") the this would be a MATCH and 1 shown as a count

If ("A36") value on May08WFA appears on cell ("A30") the this would be a MATCH and 1 shown as a count

If ("A36") value on May08WFA did not appear anywhere in rangel ("A2:A2000") then this would be a not be a MATCH and 0 shown

Hope this clears things

#### D-Spark

##### Board Regular
Ive cracked it:

=SUMPRODUCT(ISNUMBER(MATCH(Apr08WFA!A2:A3000,May08WFA!A2:A3000,0))+0)

This counts the Matches between the two sheets.
________________
I have tried to expand the matches on the formula above to introduce a condition to the Match where value for comparable ranges is limited to those with the first two left characters as "zz"

SUMPRODUCT(--(LEFT(May08WFA!\$A\$1:\$A\$4000,2)="zz"),--(TEXT(May08WFA!\$F\$1:\$F\$4000,

does not work !!!

Can anyone help?

#### D-Spark

##### Board Regular
Is this possible ??

Replies
3
Views
463
Replies
3
Views
269
Replies
4
Views
894
Replies
1
Views
3K
Replies
5
Views
461

1,191,055
Messages
5,984,391
Members
439,883
Latest member
onions44

### 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