Some kind of COUNTIF AND Statement maybe ?

[Dazzybeeguy]

If I had a spreadsheet with information as shown below, so dates in Column A and "Y" or "N" in columns B, C & D and a date in say F2

What I want is a formula that says look at the date in F2, find the corresponding date in row A:A and count how many Ys are in column B:B when the dates match.

A B C D E
4 31/12 Y N N
5 30/12 N Y N
6 29/12 N N Y

Thank you for any help you can give.

 

[Fluff]

Just col B or col B, C & D

 

[Sufiyan97]

do you want count of Y from column B only or from column B C and D

 

[Dazzybeeguy]

Just col B or col B, C & D

Hi, I would just duplicate the formula into two other cells that relate to column C and D, Or I could have in Addition to Cell F2 showing the date have another Cell ( say G2) have a validation list where they pick the relevant Column they want to count the Ys in

 

[Fluff]

Ok, how about

=COUNTIFS(A:A,F2,B:B,"Y")

 

[Sufiyan97]

Try

Book5
	A	B	C	D	E	F	G	H	I	J
1	A	B	C	D		Date	Count of Y in B	Count of Y in C	Count of Y in D
2	31-Dec	Y	N	N		29-Dec	1	0	1
3	30-Dec	N	Y	N
4	29-Dec	Y	N	Y
5
6
Sheet2

Cell Formulas
Range	Formula
G2:I2	G2	=COUNTIFS($A:$A,$F2,B:B,"Y")

 

[Fluff]

If you want a drop down, for the col

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1		A	B	C			B
2	31/12/2022	Y	N	N		30/12/2022	1
3	30/12/2022	N	Y	N
4	29/12/2022	N	N	Y
5
6
Data

Cell Formulas
Range	Formula
G2	G2	=COUNTIFS(A:A,F2,INDEX(B:D,0,MATCH(G1,B1:D1,0)),"Y")

 

[Dazzybeeguy]

Thank you for your help, perfect now

 

[Fluff]

Glad we could help & thanks for the feedback.