Formula Help using COUNTIF on a table with OR logic

[aprashar]

I have a sample table listing some contacts with email addresses.

While there are many email addresses, using the HAVEEMGEMAIL column identifies "emgscc.com" users for me.

Regardless of the email address I have two columns, REASONA and REASONB, that flag accounts that I should not delete due to their TYPE.

I want to COUNTIF the rows where:
1. HAVEEMGEMAIL = TRUE
AND
2. REASONA *OR* REASONB contains "DON'T DELETE"

I have linked the and excel file and also have provided a picture, based on your need, for review. My apologies if these links do not work as it is the first time I am sharing any type of file with the Internet:
https://1drv.ms/f/s!AveqQKlPsI3k0lH7q_RlnT0olto3

 

[63falcondude]

For others to read the layout:

Excel 2010
	A	B	C	D	E	F	G
1	NAME	EMAIL	ORGANIZATION	TYPE	REASONA	REASONB	HAVEEMGEMAIL
2	a	a@aol.com	SACRAMENTO COMMUNITY HOSPITAL	STAFF			FALSE
3	b	b@compuserve.com	KAISER	STAFF			FALSE
4	c	c@pacbell.net	FITNESS THERAPY	STAFF			FALSE
5	d	d@commodore.com	GETWELL	STAFF			FALSE
6	e	e@tandycorp.com	STANFORD HEALTH CARE	STAFF			FALSE
7	f	f@compaq.com	KAISER	MD		DON'T DELETE	FALSE
8	g	g@emgscc.com	SACRAMENTO COMMUNITY HOSPITAL	STAFF			TRUE
9	h	h@aol.com	KAISER	STAFF			FALSE
10	i	i@emgscc.com	KAISER	STAFF			TRUE
11	j	j@compuserve.com	GETWELL	STAFF			FALSE
12	k	k@emgscc.com	GETWELL	PHYSICIAN	DON'T DELETE	DON'T DELETE	TRUE
13	l	l@emgscc.com	SACRAMENTO COMMUNITY HOSPITAL	STAFF			TRUE
14	m	m@aol.com	GETWELL	STAFF			FALSE
15	n	n@emgscc.com	GETWELL	STAFF	DON'T DELETE		TRUE
16	o	o@compuserve.com	SACRAMENTO COMMUNITY HOSPITAL	MD	DON'T DELETE	DON'T DELETE	FALSE
17	p	p@aol.com	GETWELL	STAFF			FALSE
18	q	q@emgscc.com	STANFORD HEALTH CARE	PHYSICIAN	DON'T DELETE	DON'T DELETE	TRUE
19	r	r@emgscc.com	GETWELL	STAFF			TRUE
20	s	s@emgscc.com	KAISER	STAFF			TRUE
21	t	t@compuserve.com	GETWELL	STAFF	DON'T DELETE		FALSE
22	u	u@aol.com	FITNESS THERAPY	STAFF	DON'T DELETE		FALSE
23	v	v@emgscc.com	SACRAMENTO COMMUNITY HOSPITAL	STAFF	DON'T DELETE		TRUE
24	w	w@emgscc.com	SACRAMENTO COMMUNITY HOSPITAL	STAFF	DON'T DELETE		TRUE
25	x	x@aol.com	SACRAMENTO COMMUNITY HOSPITAL	STAFF	DON'T DELETE		FALSE
26	y	y@compuserve.com	KAISER	STAFF	DON'T DELETE		FALSE
27	z	z@emgscc.com	SACRAMENTO COMMUNITY HOSPITAL	STAFF	DON'T DELETE		TRUE
Sheet1

 

[Joe4]

Welcome to the Board!

I imagine that there has to be a better way than this, but it works (based on the data 63falcondude posted):

=COUNTIFS(G2:G27,"TRUE",E2:E27,"DON'T DELETE")
 +COUNTIFS(G2:G27,"TRUE",F2:F27,"DON'T DELETE")
 -COUNTIFS(G2:G27,"TRUE",E2:E27,"DON'T DELETE",F2:F27,"DON'T DELETE")

 

[63falcondude]

If you create a helper column H with the formula =OR(E2="DON'T DELETE",F2="DON'T DELETE"), then you can use the formula =COUNTIFS(G2:G27,TRUE,H2:H27,TRUE)

 

[63falcondude]

I can't reply to your PM so I will post it here.

You displayed my table so easily within my post. How did you insert that table? I did try copy and paste from excel to my post but did not have much success.

Thank you for doing that for me.
-Ajay

No problem, this is what I used:

MrExcel HTML Maker
http://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[aprashar]

"Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Thanks to Joe4 and 63falcondude. I am going to spend the next few minutes understanding their solutions backwards and forwards. I wish I came to you guys 7 hours ago.

 

[63falcondude]

Glad to help!

 

[Joe4]

You are welcome!

To learn more about COUNTIFS, see: https://www.techonthenet.com/excel/formulas/countifs.php

The issue is that criteria in COUNTIFS is treated as an AND condition, and you have an OR condition in your request.
So, the logic in my solution was to do it this way:
1. COUNT the number of records that have TRUE in Column G, and "DON'T DELETE" in column E
and then add it to the following:
2. COUNT the number of records that have TRUE in Column G, and "DON'T DELETE" in column F
however, there are people "DON'T DELETE" in columns E and F, so they are being double-counted, so we must subtract the following:
3. COUNT the number of records that have TRUE in Column G, and "DON'T DELETE" in column E, and and "DON'T DELETE" in column F

Hope that makes sense!