Count If Multiple Criteria

[kparadise]

Hello,

I need to count using two criteria. I have a range of user emails; AV3:BA100 in Sheet1; these are the data points I am trying to analyze. I also have a range of email domains for reference, which will come into play later in A1:A4 in Sheet2.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Sheet1
[/TD]
[TD]AV
[/TD]
[TD]AW
[/TD]
[TD]AX
[/TD]
[TD]AY
[/TD]
[TD]AZ
[/TD]
[TD]BA
[/TD]
[TD]countAV
[/TD]
[TD]countAW
[/TD]
[TD]countAX
[/TD]
[TD]countAY
[/TD]
[TD]countAZ
[/TD]
[TD]countBA
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]kyle@gmail.com
[/TD]
[TD]tony@hotmail.com
[/TD]
[TD]dad@gmail.coom
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]sara@yahoo.com
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]jim@gmail.com
[/TD]
[TD]john@gmail.com
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]kyle@gmail.com
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]kyle@gmail.com
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]tony@hotmail.com
[/TD]
[TD]sara@yahoo.com
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Sheet2
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]gmail.com
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]aol.com
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]excel.com
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]word.com
[/TD]
[/TR]
</tbody>[/TABLE]


What I am trying to do is in cell countAV, look at the value in AV, and count how many times that exact email appears in the entire AV3:BA100; BUT the email domain needs not to be found in Sheet2 A1:A4. You can see the red values which I am looking for. I can do each count, but I do not know how to put them together.

=COUNTIFS($AV$3:$BA$100, AV3)
=SUMPRODUCT(--ISNA(MATCH(AV3, {"Sheet2!$A$1:$A$4"},0)))

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L
2							countAV	countAW	countAX	countAY	countAZ	countBA
3	kyle@gmail.com	tony@hotmail.com	dad@gmail.com				0	2	0	0	0	0
4	sara@yahoo.com						2	0	0	0	0	0
5	jim@gmail.com	john@gmail.com					0	0	0	0	0	0
6	kyle@gmail.com						0	0	0	0	0	0
7	kyle@gmail.com						0	0	0	0	0	0
8	tony@hotmail.com	sara@yahoo.com					2	2	0	0	0	0
Names

Cell Formulas
Range	Formula
G3	{=IF(ISNUMBER(SEARCH(Sheet2!$A$1:$A$4,A3)),0,COUNTIF(Names!$A$3:$F$8,Names!A3))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[kparadise]

I actually changed the first part of that If statement to my original SUMPRODUCT function.

{=IF(SUBPRODUCT(--ISNUMBER(SEARCH(Sheet2!$A$1:$A$4,A3))),0,COUNTIF(Names!$A$3:$F$8,Names!A3))}

 

[Fluff]

Does it work?

 

[kparadise]

Yes, thank you!

 

[Fluff]

You're welcome