Count entries "1"

[omer1234]

Admin - I am not trying to be rude, but I genuinely am struggling & not trying to waste anyone's time !!!!!!

I have tried to google but unable to find a resolution


Given this data set, How do I extract the number of times "1" is recorded on a specific date..... OR "3" but I am assuming it will be the same formula just different cells

Thanks

B-Team	B-Team	B-Team	C-Team	B-Team	B-Team	B-Team	A-Team
02/27/24 13:30:43	03/01/24 00:00:00	02/27/24 12:45:18	02/02/24 00:00:00	02/27/24 11:00:03	02/02/24 00:00:00	02/27/24 12:30:24	03/01/24 00:00:00
3.00	1.00	0.00	0.00	0.00	0.00	0.00	0.00
3.00	1.00	0.00	0.00	1.00	0.00	0.00	0.00
3.00	1.00	0.00	0.00	0.00	0.00	0.00	1.00
0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
0.00	0.00	1.00	1.00	0.00	0.00	1.00	0.00
0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
3.00	0.00	0.00	0.00	0.00	1.00	0.00	0.00
3.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
			Criteria	1.00
		A-Team	B-Team	C-Team	D-Team
	27-Feb
	1-Mar
	2-Feb

 

[etaf]

does this work
=SUMPRODUCT(($A$5:$H$12=$E$15)*($A$2:$H$2=C$16)*(INT($A$3:$H$3)=$B18))

INT($A$3:$H$3) is because you have times in the cells , so INT is to make it just the date to compare with the criteria date

Book4
	A	B	C	D	E	F	G	H
1
2	B-Team	B-Team	B-Team	C-Team	B-Team	B-Team	B-Team	A-Team
3	2/27/24 13:30	3/1/24 0:00	2/27/24 12:45	2/2/24 0:00	2/27/24 11:00	2/2/24 0:00	2/27/24 12:30	3/1/24 0:00
4
5	3	1	0	0	0	0	0	0
6	3	1	0	0	1	0	0	0
7	3	1	0	0	0	0	0	1
8	0	0	0	0	0	0	0	0
9	0	0	1	1	0	0	1	0
10	0	0	0	0	0	0	0	0
11	3	0	0	0	0	1	0	0
12	3	0	0	0	0	0	0	0
13
14
15				Criteria	1
16			A-Team	B-Team	C-Team	D-Team
17
18		27-Feb	0	3	0	0
19		1-Mar	1	3	0	0
20		2-Feb	0	1	1	0
Sheet1

Cell Formulas
Range	Formula
C18:F20	C18	=SUMPRODUCT(($A$5:$H$12=$E$15)*($A$2:$H$2=C$16)*(INT($A$3:$H$3)=$B18))

 

[omer1234]

does this work
=SUMPRODUCT(($A$5:$H$12=$E$15)*($A$2:$H$2=C$16)*(INT($A$3:$H$3)=$B18))

INT($A$3:$H$3) is because you have times in the cells , so INT is to make it just the date to compare with the criteria date

Book4
	A	B	C	D	E	F	G	H
1
2	B-Team	B-Team	B-Team	C-Team	B-Team	B-Team	B-Team	A-Team
3	2/27/24 13:30	3/1/24 0:00	2/27/24 12:45	2/2/24 0:00	2/27/24 11:00	2/2/24 0:00	2/27/24 12:30	3/1/24 0:00
4
5	3	1	0	0	0	0	0	0
6	3	1	0	0	1	0	0	0
7	3	1	0	0	0	0	0	1
8	0	0	0	0	0	0	0	0
9	0	0	1	1	0	0	1	0
10	0	0	0	0	0	0	0	0
11	3	0	0	0	0	1	0	0
12	3	0	0	0	0	0	0	0
13
14
15				Criteria	1
16			A-Team	B-Team	C-Team	D-Team
17
18		27-Feb	0	3	0	0
19		1-Mar	1	3	0	0
20		2-Feb	0	1	1	0
Sheet1

Cell Formulas
Range	Formula
C18:F20	C18	=SUMPRODUCT(($A$5:$H$12=$E$15)*($A$2:$H$2=C$16)*(INT($A$3:$H$3)=$B18))

THanks YOU BUD

 

[etaf]

you are welcome