Countif formula to not count same cell twice

[raul8]

Hello everyone,

I have this formula (Thank you Eric W) that is counting the same cell twice because the "if" condition is present on that same cell twice but the intend is to look at that cell and if any of the conditions exist, to count it once only. Need some help in solving that. Thank you all!: (on the sample below, I am getting a return of 4 but I expect and it should be a return of 3)

=SUMPRODUCT(COUNTIFS(Data!C:C,{"*Hold*";"*P5*";"*P6*"},Data!AB:AB,"*BP*",Data!V:V,{"*test5*","*test3*"}))

Copy of 10_05_2021 Portfolio Validation.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	a	b	c	d	e	f	g	h	i	j	k	l	m	n	o	p	q	r	s	t	u	v	w	x	y	z	aa	ab
2	77864	area	Hold	text	text	2 - High	text		8/18/2020 8:00 PM	8/18/2020 8:00 PM	4/29/2021 8:00 PM		150000	228000	Yellow	Green	text	7/30/2020 8:00 PM	8/4/2020 8:00 PM	8/3/2020 8:00 PM	text		9/9/2021 8:43 AM	9/9/21 - text	5000001	Epic	New	\Management\BP
3	71321	area	P5- text	text	text	3 - Medium	text	8/30/2021 8:00 PM	7/18/2021 8:00 PM	7/18/2021 8:00 PM	11/12/2021 8:00 PM	12/17/2021 8:00 PM	58000	58000	Green	Green	text				text		8/30/2021 5:20 PM	9/9/21 - text	540000	Epic	Active	\Management\BP
4	106537	area	P5- text	text	text	2 - High	text	11/30/2021 7:00 PM	8/29/2021 8:00 PM	8/22/2021 8:00 PM	11/30/2021 7:00 PM	12/1/2021 7:00 PM	55000	49000	Green	Green	text	4/30/2021 8:00 PM	7/4/2021 8:00 PM	6/29/2021 8:00 PM	text	Test 1	9/13/2021 7:35 AM	9/9/21 - text	30000	Epic	Active	\Management\BP
5	99730	area	P5- text	text	text	1 - Critical	text	3/13/2022 8:00 PM	3/31/2021 8:00 PM	3/31/2021 8:00 PM	3/30/2022 8:00 PM	3/30/2022 8:00 PM	800000	800000	Green	Green	text	3/28/2021 8:00 PM		3/28/2021 8:00 PM	text		6/18/2021 3:50 PM	9/9/21 - text	8000000	Epic	Active	\Management\BP
6	78140	area	P5- text	text	text	1 - Critical	text	2/28/2022 4:00 AM	12/10/2020 7:00 PM	12/10/2020 7:00 PM	10/14/2021 8:00 PM	2/27/2022 7:00 PM	159000	250000	Green	Green	text	5/4/2020 8:00 PM	11/2/2020 7:00 PM	6/18/2020 8:00 PM	text	Test2;test4;test5	8/3/2021 2:14 PM	9/9/21 - text	510000	Epic	Active	\Management\BP
7	100849	area	P5- text	text	text	3 - Medium	text	12/30/2021 7:00 PM	8/31/2021 8:00 PM	8/31/2021 8:00 PM	11/29/2021 7:00 PM	11/29/2021 7:00 PM	66000	66000	Green	Green	text				text		8/20/2021 10:22 AM	9/9/21 - text	35000	Epic	Active	\Management\BP
8	100802	area	P5- text	text	text	2 - High	text	3/21/2022 7:00 PM	9/20/2021 8:00 PM	9/20/2021 8:00 PM	4/4/2022 7:00 PM	4/4/2022 7:00 PM	218000	218000	Green	Green	text	6/9/2021 12:00 AM	9/20/2021 12:00 AM	7/6/2021 8:00 PM	text		9/22/2021 8:33 AM	9/9/21 - text		Epic	Active	\Management\BP
9	106791	area	P5- text	text	text	2 - High	text	11/29/2021 7:00 PM	8/19/2021 8:00 PM	8/19/2021 8:00 PM	12/9/2021 7:00 PM	12/9/2021 7:00 PM	950000	950000	Green	Green	text	4/21/2021 12:00 AM	8/23/2021 12:00 AM	4/21/2021 8:00 PM	text	test1	9/8/2021 9:45 AM	9/9/21 - text	3750000	Epic	Active	\Management\BP
10	92436	area	P5- text	text	text	2 - High	text	9/30/2021 8:00 PM	3/14/2021 8:00 PM	3/14/2021 8:00 PM	8/30/2021 8:00 PM	10/15/2021 8:00 PM	165000	180000	Green	Green	text	1/26/2021 7:00 PM	3/10/2021 7:00 PM	2/8/2021 7:00 PM	text	test4,test3	8/3/2021 6:04 PM	9/9/21 - text	2000000	Epic	Active	\Management\BP
11	94233	area	P5- text	text	text	3 - Medium	text	1/31/2022 7:00 PM	3/7/2021 7:00 PM	3/7/2021 7:00 PM	3/17/2022 8:00 PM	8/25/2022 8:00 PM	125000	125000	Green	Green	text	12/17/2020 7:00 PM	2/7/2021 7:00 PM	12/30/2021 7:00 PM	text	test1	8/24/2021 3:52 PM	9/9/21 - text	1000000	Epic	Active	\Management\BP
12	55611	area	P6- text	text	text	3 - Medium	text	3/25/2022 1:00 AM	2/25/2021 7:00 PM	2/25/2021 7:00 PM	3/24/2022 8:00 PM	3/24/2022 8:00 PM	72000	72000	Green	Green	text	3/3/2020 7:00 PM	1/18/2021 7:00 PM	4/23/2020 8:00 PM	text	test3,test5	2/8/2021 11:55 AM	9/9/21 - text	770000	Epic	Active	\Management\BP
13	78728	area	P6- text	text	text	2 - High	text	8/31/2021 8:00 PM	1/5/2021 7:00 PM	1/5/2021 7:00 PM	7/31/2021 8:00 PM	11/28/2021 7:00 PM	130000	130000	Red	Yellow	text	7/29/2020 8:00 PM	10/7/2020 8:00 PM	10/1/2020 8:00 PM	text	test4	9/13/2021 7:32 AM	9/9/21 - text	500000	Epic	Active	\Management\BP
Sheet4

 

[Eric W]

A somewhat more compact way to write Fluff's formula:

=COUNT(FILTER(ROW(C2:C100),MMULT(--ISNUMBER(SEARCH({"Hold","P5","P6"},C2:C100)),{1;1;1})*ISNUMBER(SEARCH("BP",AB2:AB100))*MMULT(--ISNUMBER(SEARCH({"test5","test3"},V2:V100)),{1;1})))

This works without the FILTER like this:

=SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH({"Hold","P5","P6"},C2:C100)),{1;1;1})*ISNUMBER(SEARCH("BP",AB2:AB100))*MMULT(--ISNUMBER(SEARCH({"test5","test3"},V2:V100)),{1;1})>0))