Countifs exclude based on other criteria

[dekeda]

I have a data set where I am using a "countifs" function to count entries based on ID and date range. I need to integrate another data set with a unique format, but some of the same columns, and then ONLY count unique entries based on ID, date range, and exclude entries based on a third criteria (appointment number) that also have the same ID and appointment number in the first set of data. I cannot just combine the two data sets and remove duplicates. Hope this makes sense.

I can't copy the file here as it is proprietary, but to simply and boil it down I basically have the following data set:

The 2nd data set (I formatted it the same for simplicity) would be like this:

I need a formula that references a cell with the ID, and then counts the unique entries in the second data set where the Number column is not found in the first data set. So for "Aa0001" it would exclude the first two entries, and count everything highlighted yellow, and for "Bb0001" it would exclude the first two entries and count everything highlighted in blue.

Many thanks in advance for your help!

 

[Eric W]

Welcome to the forum.

I'm a little confused, are you looking for a count of the unmatched items (the yellow and blue cells), or a list of the unmatched items?

	A	B	C	D	E	F	G	H	I
1	ID				ID	Number		ID	Unmatched entries
2	Aa0001	1			Aa0001	1		Bb0001	4
3	Aa0001	2			Aa0001	2
4	Aa0001	3			Aa0001	15
5	Aa0001	4			Aa0001	16
6	Aa0001	5			Aa0001	17
7	Aa0001	6			Aa0001	18
8	Aa0001	7			Aa0001	19
9	Aa0001	8			Aa0001	20
10	Bb0001	9			Bb0001	9
11	Bb0001	10			Bb0001	10
12	Bb0001	11			Bb0001	21
13	Bb0001	12			Bb0001	22
14	Bb0001	13			Bb0001	23
15	Bb0001	14			Bb0001	24
16

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula I2 =SUMPRODUCT(--ISERROR(MATCH(E2:E15&"|"&F2:F15,A2:A15&"|"&B2:B15,0)),--(E2:E15=H2)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Here's a formula that finds the count. Let me know if this is what you're looking for.

 

[dekeda]

That's exactly what I need! Works beautifully. Thank you for the quick reply, you just saved me hours worth of research and work. Simple yet ingenious way of identifying the unique values and adding them up.

Thanks again!

 

[Eric W]

Glad to help!