Countifs multiple criteria including an array

[CodenameAter]

Would someone explain how to create a formula where I ask whether a particular value is equal to multiple criteria, including anything matching the criteria in any column of an array?

For example, in the made-up data below, I know how to write a COUNTIFS formula to ask if any data in ID1 and ID2 are equal to any other row except the current row and if the data also falls within a particular time window. However, if I want to also ask whether there is a "1" in SB-2, SB-3 or SB-4, the only way I know to do this is by adding criteria for each row. However, this is just an example and the actual data has many SB columns. I was wondering if I could ask if the array starting with SB-2 has the value 1 in any row of that array?

Let me know if this is making sense. Thank you,

ID1	ID2	DATE&TIME	SB-2	SB-3	SB-4
A	C	2021-05-13 11:52:00	1
B		2021-05-13 11:52:01
A	C	2021-05-13 11:52:02
D	E	2021-05-13 11:53:00	1
E	D	2021-05-13 11:53:14		1
D	E	2021-05-13 11:53:28		1
E	D	2021-05-13 11:53:42			1
E	D	2021-05-13 11:53:56			1
D	E	2021-05-13 11:54:10		1
E	D	2021-05-13 11:54:24		1
D	E	2021-05-13 11:54:38	1
B	C	2021-05-13 11:54:41	1
B	C	2021-05-13 11:54:42		1
A	F	2021-05-13 11:54:45	1
F	A	2021-05-13 11:54:49
D	E	2021-05-13 11:54:56	1
C	B	2021-05-13 11:54:57
D	E	2021-05-13 11:55:08		1
E	D	2021-05-13 11:55:09		1
D	E	2021-05-13 11:55:10		1

 

[Toadstool]

Hi CodenameAter,

Is this the type of thing you were looking for?

CodenameAter.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	ID1	ID2	DATE&TIME	SB-2	SB-3	SB-4			Date&Time		Result
2	A	C	5/13/2021 11:52	1				Start	13-May-2021 11:54:00		3
3	B		5/13/2021 11:52					End	13-May-2021 11:54:50
4	A	C	5/13/2021 11:52
5	A	A	5/13/2021 11:53	1
6	E	D	5/13/2021 11:53		1
7	D	E	5/13/2021 11:53		1
8	E	D	5/13/2021 11:53			1
9	E	D	5/13/2021 11:53			1
10	D	E	5/13/2021 11:54		1
11	D	D	5/13/2021 11:54	1
12	B	C	5/13/2021 11:54	1
13	B	B	5/13/2021 11:54		1
14	A	A	5/13/2021 11:54	1
15	A	A	5/13/2021 11:54	1
16	D	D	5/13/2021 11:54	1
17	C	B	5/13/2021 11:54
18	D	E	5/13/2021 11:55		1
19	D	D	5/13/2021 11:55	1	1	1
20	D	E	5/13/2021 11:55		1
21
22
Sheet1

Cell Formulas
Range	Formula
K2	K2	=SUMPRODUCT(--($A$2:$A$21=$B$2:$B$21)*($C$2:$C$21>=$I$2)*($C$2:$C$21<=$I$3)*($D$2:$D$21=1))

 

[Eric W]

Or maybe something like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	ID1	ID2	DATE&TIME	SB-2	SB-3	SB-4			Date & Time		ID1	Result
2	A	C	5/13/2021 11:52	1				Start	5/13/2021 11:00		A	2
3	B		5/13/2021 11:52					End	5/13/2021 12:00		B	2
4	A	C	5/13/2021 11:52								C	0
5	D	E	5/13/2021 11:53	1							D	7
6	E	D	5/13/2021 11:53		1						E	5
7	D	E	5/13/2021 11:53		1						F	0
8	E	D	5/13/2021 11:53			1
9	E	D	5/13/2021 11:53			1
10	D	E	5/13/2021 11:54		1
11	E	D	5/13/2021 11:54		1
12	D	E	5/13/2021 11:54	1
13	B	C	5/13/2021 11:54	1
14	B	C	5/13/2021 11:54		1
15	A	F	5/13/2021 11:54	1
16	F	A	5/13/2021 11:54
17	D	E	5/13/2021 11:54	1
18	C	B	5/13/2021 11:54
19	D	E	5/13/2021 11:55		1
20	E	D	5/13/2021 11:55		1
21	D	E	5/13/2021 11:55		1
Sheet1

Cell Formulas
Range	Formula
L2:L7	L2	=SUMPRODUCT(($A$2:$A$21=K2)*($C$2:$C$21>=$I$2)*($C$2:$C$21<=$I$3)*SIGN(MMULT(--($D$2:$F$21=1),TRANSPOSE(COLUMN($D$2:$F$2)^0))))

I didn't quite grasp

if any data in ID1 and ID2 are equal to any other row except the current row

so I left out ID2. But see if this is the right idea for looking at multiple columns.

 

[CodenameAter]

so I left out ID2. But see if this is the right idea for looking at multiple columns.

Thank you. Yes, I think this is the right idea. This is just sample data, so really I just need to understand the formula you use to calculate if multiple criteria are met on a particular row (i.e. ID1, ID2 and Date&Time in a row all meet the right criteria) then return a value if any column in an array on the same row also equals a particular value.

For example, rows 5 and 7 meet the criteria, so each of those rows should return a value that would populate in column G.

Let me read into the SIGN and MMULT functions because I am not familiar with them and get back to you. Stay tuned.