Help with Match syntax in formula

[D-Spark]

I have the following formula:

=SUMPRODUCT(--(LEFT(May08WFA!$A$1:$A$4000,2)="x-"),--(ISERROR(MATCH(May08WFA!$A$1:$A$4000,Apr08WFA!$A$1:$A$4000,0)=TRUE)))


I want to amend the part of the formula “(LEFT(May08WFA!$A$1:$A$4000,2)="x-")” to now look at column B range 1:4000 on the same sheet and isolate only values that do not contain a comma in the text.

LEFT function not needed for this part.

Then Is Error (Match B1:B4000) between the two sheet

 

[DonkeyOte]

you're missing a - before first criteria test which is making result negative...

change to

=SUMPRODUCT(--

(my bad as one of my earlier examples was missing this in the syntax)

 

[D-Spark]

Workbook is set up as follows:
3 tabs "cover", "Apr08WFA", "May08WFA"

Apr08WFA...A1="Account"
Apr08WFA...B1="Name
Apr08WFA...A2="darren t"
Apr08WFA...B2="darren test"
May08WFA...A1="Account"
May08WFA...A2="SmithS4"
May08WFA...A3="SmithS5"
May08WFA...A4="SouthWestPims"
May08WFA...A5="x-spark20"
May08WFA...A6="zzASP"
May08WFA...A7="zzbcuser"
May08WFA...B1="Name"
May08WFA...B2="Smith, Simon"
May08WFA...B3="Smith, Sara"
May08WFA...B4="South West Pims"
May08WFA...B6="Steven Park"
May08WFA...B7="zzASP"

Cover...B7 ="New additions"
Cover...B8 ="New x- additions"
Cover...B7 ="New zz dditions"
Cover...B10 ="New non "," additions"
Cover...B11 ="New "," additions"

Cover...D7 =SUMPRODUCT(-(ISERROR(FIND(",",May08WFA!$B$2:$B$3))=TRUE),--(ISBLANK(May08WFA!$B$2:$B$3)=FALSE),--(ISERROR(MATCH(May08WFA!$B$2:$B$3,Apr08WFA!$B$2:$B$3,0)=TRUE)))


formal showing -4 in the example above where they were +4 new addtion between Apr and May for non comma entries, however as one was and x- account and two were zz accounts the expected return should be +1 for new non comma accounts



D7 to equal: count of new aditions between the two sheets
D8 to equal: count of new x- Account
D9 to equal: count of new zz accounts
D10 to equal: count of non comma additions taken from B ranges where they arent x- accounts nor zz account
D11 to equal: count of "," additions

 

[DonkeyOte]

D-Spark - see prior post.

 

[D-Spark]

Have made these amendments and it resolves the minus issue, however the issues on my last post are still outstanding.

I have provided teh cell references and value so that it can be replicated and the formula advised on:

thanks

 

[DonkeyOte]

Hey, can you just double check your sample data -- in particular names on May08WFA sheet -- think your missing one or they're out of sync when compared to account name... when I use range B2:B7 and the formula provided I get 3 rather than 4 as you state and wonder if it's because of a missing name.

Thanks,

 

[D-Spark]

my error correct range and values are:

Apr08WFA...A1="Account"
Apr08WFA...B1="Name
Apr08WFA...A2="darren t"
Apr08WFA...B2="darren test"
May08WFA...A1="Account"
May08WFA...A2="SmithS4"
May08WFA...A3="SmithS5"
May08WFA...A4="SouthWestPims"
May08WFA...A5="x-spark20"
May08WFA...A6="zzASP"
May08WFA...A7="zzbcuser"
May08WFA...B1="Name"
May08WFA...B2="Smith, Simon"
May08WFA...B3="Smith, Sara"
May08WFA...B4="South West Pims"
May08WFA...B5="Steven Park"
May08WFA...B6="zzASP"
May08WFA...B7="BeCrpt User"

Cover...B7 ="New additions"
Cover...B8 ="New x- additions"
Cover...B7 ="New zz dditions"
Cover...B10 ="New non "," additions"
Cover...B11 ="New "," additions"

 

[DonkeyOte]

OK, I'm just going to post up the formula for D10 as this is one of the more complex.

D10 = count of non comma additions taken from B ranges where they arent x- accounts nor zz account

a SUMPRODUCT in this instance is essentially working along the lines of a COUNTIF but with multiple criteria... each --(...) is a test so you just need to add all the tests into your formula... so the final formula looks like this...

=SUMPRODUCT(
--(ISERROR(FIND(",",MAY08WFA!$B$2:$B$7))=TRUE),
--(LEFT(MAY08WFA!$B$2:$B$7,2)<>"zz"),
--(LEFT(MAY08WFA!$B$2:$B$7,2)<>"x-"),
--(ISBLANK(MAY08WFA!$B$2:$B$7)=FALSE),
--(ISERROR(MATCH(MAY08WFA!$B$2:$B$7,APR08WFA!$B$2:$B$7,0)=TRUE))
)

each argument is separated in this instance by a comma, so breaking out each argument you're doing the following:

Find instances in B where no comma can be found (, entries need to be discounted)
--(ISERROR(FIND(",",MAY08WFA!$B$2:$B$7))=TRUE),

Find instances in B where left two characters do not = "zz" (zz need to be discounted)
--(LEFT(MAY08WFA!$B$2:$B$7,2)<>"zz"),

Find instances in B where left two characters do not = "x-" (x- need to be discounted)
--(LEFT(MAY08WFA!$B$2:$B$7,2)<>"x-"),

Find instances in B where B is NOT blank (blanks need to be discounted)
--(ISBLANK(MAY08WFA!$B$2:$B$7)=FALSE),

Find entries in B that do not appear on other sheet in same range
--(ISERROR(MATCH(MAY08WFA!$B$2:$B$7,APR08WFA!$B$2:$B$7,0)=TRUE))

Each test will result in a 0 or 1 value where 0 = fail test, 1 = meets test
(the -- forces TRUE/FALSE to 0/1)

By multiplying the results of these tests together (sumproduct) you will get either a 0 or 1 value for each row in the range. So it follows that wherever result for any one test that fails in a given row you will get a final result of 0 even if all the other test held true because 1*1*1*1*0 = 0

Hopefully by understanding how the SUMPRODUCT works you can work on creating the other formulae yourself.