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]

--(ISERROR(FIND(",",May08WFA!$B$1:$B$4000))=TRUE)

 

[D-Spark]

This appears to be counting blank cells also ????

 

[DonkeyOte]

you would need to add an additional clause to exclude blanks

-(ISERROR(FIND(",",May08WFA!$B$1:$B$4000))=TRUE),--(ISBLANK(May08WFA!$B$1:$B$4000)=FALSE)

 

[D-Spark]

Private message sent to enable sending of spreadsheet to highlight issue

 

[DonkeyOte]

please keep issue within thread -- post up issues with the solution(s) provided thus far.

 

[D-Spark]

Can I upload the worksheet in question, as the solution is not returning expect values - If so How?

 

[DonkeyOte]

reading your post thus far the below formula does what you requested does it not ?

=SUMPRODUCT(--(ISERROR(FIND(",",May08WFA!$B$1:$B$4000))=TRUE),--(ISBLANK(May08WFA!$B$1:$B$4000)=FALSE),
--(ISERROR(MATCH(May08WFA!$A$1:$A$4000,Apr08WFA!$A$1:$A$4000,0)=TRUE)))

Finds non-blank entries in B range that do not contain a comma that are also unique to that sheet.

 

[D-Spark]

I am getting "-1" in the cell containing the formula when the expected outturn should be +1, ie one new unique entry on sheet May08WFA where this entry was no within Apr08WFA

Formula:

=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)))


Only change is that I I have reduced the range

 

[DonkeyOte]

OK - can you tell us what you entries you have in B2:B3 on both Apr08WFA and May08WFA sheets so we can replicate ?