How to search multiple columns on seperate sheets for more than one criteria

Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).

Hi,

In E2 and copy down:

=SUMPRODUCT(--(Sheet2!$B$2:$B$4&"|"&Sheet2!$C$2:$C$4=Sheet1!B2&"|"&Sheet1!C2))

Regards

Originally Posted by GSmith8

the above should return 7

Can you please explain how you will get 7 ??

Hi XOR,

I have used this formula as you mentioned. It returns a value to C however sometimes this is more than 1. The data in these 2 fields is that unique that there should be no times that the data in B&C on the 2 sheets match.

I need to be sure it is counting only if B&C match each other. They both have to match for me to count it.

Attached sheet as i have it at the moment

Sorry - I don't understand. Your original post contained: "If data in column B appears on both sheets & data from column C appears in both sheets then return me the number of time this happens".

And I'm not sure what you mean about the data being unique.

Just to take one example, in Sheet2, rows 863, 875, 882, 883 & 1042 are the five identical entries with a TransactionDate of 07/04/2013 18:06 and a Net PSL of 22 - hence the formulas in Sheet1 rows 254-257 & 260 return a value of 5.

Regards

Originally Posted by GSmith8

For the workbook it should be 3?

Try the below suggestion in Post #3 attachment file.

In E2 Cell of Sheet1

=IF(COUNTIFS($B$2:B2,B2,$C$2:C2,C2)=2,0,COUNTIFS(Sheet2!B:B,Sheet1!B2,Sheet2!C:C,Sheet1!C2))

Drag it down…

As per your Post #6 confirmation it will get the total result as 3.