CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
scripter78
scripter78

Reputation: 1177

Count if value exists in two different sheets

I need to count the duplicates between two different sheets with a couple additional requirements. Status needs to "Not Active" and Type Needs to be either "Install" or "Upgrade" So in the example in the image below the total account numbers that are duplicated and fall under the additional requirements equal 1 for account 123456 I thought about possibly using vlookup but honestly I have no clue how you would mix that with a countif situation.

enter image description here

Upvotes: 0

Views: 1192

Answers (2)

EEM
EEM

Reputation: 6660

This solution uses an additional column in both data sets to identify the records to be compared and an array formula to count the duplicated records.

First add a column in each data set (use column “E”) name it “Key” and enter this formula for all records:

=IF(AND(EXACT(C2,"Not Active"),OR(EXACT(D2,"Install"),EXACT(D2,"Upgrade"))),
CONCATENATE(A2,CHAR(133),B2),"")

Can use this version of the same formula:

=IF(AND(C2="Not Active",OR(D2="Install",D2="Upgrade")),
A2&CHAR(133)&B2,"")

The above formula identifies the records complying with the required criteria:

Status need to be "Not Active" and Type Needs to be either "Install" or "Upgrade"

Then in "Sheet3" or where you want to have the count of duplicated records enter the following Array Formula (press CTRL+SHIFT+ENTER to enter the formula as Array Formula) :

{=SUM(IF(Sheet1!$E$2:$E$4="",0,COUNTIFS(Sheet2!$E$2:$E$4,Sheet1!$E$2:$E$4)))}

The above formula counts the common records that comply with the criteria in both data sets (extend the ranges for each worksheet as required excluding the headers).

Upvotes: 1

user3476534
user3476534

Reputation: 220

Create a column e on your second data sheet, in cell e2, for example, =a2&"-"&b2&"-"&c2. Then copy down. The idea is to concatenate every column should be unique. Do the same in the first data sheet. Then, cell f2 in the first data sheet should be =countif('other sheet'!e2:e50,e2) (used 50, but replace with how many rows are in the second data sheet). Copy the formula down, and anything indicated in column f of a 1 or more is a duplicate. Simply countif over the column f range of ">0".

Upvotes: 0

Related Questions