COUNTIFS with multiple and/or - how to replace

[lukasz_rz]

Hi Guys.

Here is what I'm dealing with now. I need to check if the row follows the multiple criteria:

[TABLE="width: 320"]
<tbody>[TR]
[TD]Status (column)[/TD]
[TD]Prio (column)[/TD]
[TD]Days (column)[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]4[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]4[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

To explain, the row should have:
status a, prio 1 and days 2,
or
status b, prio 1 and days 2
or... so on so on until the end of table.

I was able to use DCOUNTA formula to count the number of rows following that set of criteria, however what I need to achieve is to know for each row, if it follows the requirements or not. Anyone could help?

Thx in advance.

 

[FormR]

however what I need to achieve is to know for each row, if it follows the requirements or not.

Hi, something like this?

Excel 2013/2016
	A	B	C	D
1	Status	Prio	Days	Follows ?
2	a	1	2	Yes Follows
3	b	1	2	Yes Follows
4	a	2	4	Does not follow
5	b	2	4	Does not follow
6	a	3	5	Does not follow
7	b	3	5	Does not follow
8	a	4	7	Does not follow
9	b	4	7	Does not follow
10	c		7	Does not follow
11	d		7	Does not follow
12	e		7	Does not follow
13	f		7	Does not follow
Sheet1

Cell Formulas
Range	Formula
D2	=IF(AND(OR(A2={"a","b"}),B2=1,C2=2),"Yes Follows","Does not follow")

 

[lukasz_rz]

I was not precise enough.
This is just a list of conditions that I need to meet in another worksheet, where I have a lot of data. From that other worksheet, I need to figure out for each row, if it follows one of the criteria that I had provided in the table (12 criteria). For each of that rows I need to know if:
- status=a & prio=1 & days=2
or
- status=b & prio=1 & days=2
or
- ... other 10 criteria from the table

if it follows at least one, I would like to mark it somehow.

 

[FormR]

I I need to figure out for each row, if it follows one of the criteria that I had provided in the table (12 criteria)

Hi, something like this then..:

Important Note: for this to work, the blank cells in the criteria table need to contain the formula ="" (See B10 in example below)

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	Status	Prio	Days			Status	Prio	Days	Follows
2	a	1	2			a	1	2	Yes it follows
3	b	1	2			z	1	3	No it does not
4	a	2	4			c		7	Yes it follows
5	b	2	4			b	2	4	Yes it follows
6	a	3	5			b	2	5	No it does not
7	b	3	5			b	3	5	Yes it follows
8	a	4	7			c	3	5	No it does not
9	b	4	7			e	2	7	No it does not
10	c		7			e		7	Yes it follows
11	d		7
12	e		7
13	f		7
Sheet1

Cell Formulas
Range	Formula
I2	=IF(SUMPRODUCT(COUNTIFS(F2,$A$2:$A$13,G2,$B$2:$B$13,H2,$C$2:$C$13)),"Yes it follows","No it does not")
B10	=""

 

[lukasz_rz]

Yes! That is exactly what I needed! I was thinking of using SUMPRODUCT, but I was not sure how to do that.

One remark: I could not use ="" for prio, as this must be from 1 to 4, so I just replaced it with >0.

Thanks!!!!