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.

Sheet1	AV	AW	AX	AY	AZ	BA	countAV	countAW	countAX	countAY	countAZ	countBA
3	kyle@gmail.com	tony@hotmail.com	dad@gmail.coom				0	2	0
4	sara@yahoo.com						2
5	jim@gmail.com	john@gmail.com					0	0
6	kyle@gmail.com						0
7	kyle@gmail.com						0
8	tony@hotmail.com	sara@yahoo.com					2	2

<tbody>
</tbody>

Sheet2	A
1	gmail.com
2	aol.com
3	excel.com
4	word.com

<tbody>
</tbody>

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