I have two sheets in Google Sheets. In one I have two columns with values I need to find in two columns of the second one and return number of matches for each row in the first one.
=Iferror(INDEX ( Leads!$C$2:C$1000, ( MATCH ( $A2 & $B2,Leads!$A$2:$A$1000 & Leads!$B$2:$B$100, 0 ))),0)
I've been using this function and then assigning it 1 or 0 values with IF function, but it works only for a range without more than one match.
How to do it when there are more matches?
You have not mentioned what format your data is in, but please try:
=COUNTA(QUERY(Leads!A:B,"SELECT A WHERE A = '"&Sheet1!A1&"' and B = '"&Sheet1!B1&"' "))
copied down to suit.
It seems like a countif formula would work from what you described.
If I have misunderstood your question, please share a screenshot or example of your data.
If you're specifically looking for rows which match data in both columns, you could add a helper column in column C for both sheets: =Concat(A1,B1) in cell C1 and drag down. Then in Cell D1 of Sheet 1, use this formula:
=Countif('Sheet2!'C$1:C$1000,C1)
And drag down.
