Countif with hlookup

[kieran0114]

Hello everyone. First post here but looking for some help.

In the attached, I am looking to write a formula in cells b9:b12 that would look at the current date (cell a7) and find that date in the header of a1:f5 and count the number of times each letter shows in that column based off the corresponding letters in a9:a12. In the example, b9=1, b10=1, and b11=1


I can’t seem to figure this out and I’m sure it is easy for most ya’ll, but looking for any help!

Thanks!

 

[kieran0114]

One thing to add, I would like to return a blank in cells b9:b12 if the letter does not show! So B12 in the example above would be blank.

 

[Phuoc]

Try this in B9:

=LET(a,COUNTIF(INDEX($B$2:$F$5,0,MATCH($A$7,$B$1:$F$1,0)),$A9),IF(a=0,"",a))

 

[SunnyAlv]

try

B9

=SUMPRODUCT(--(FILTER($B$2:$F$5,($B$1:$F$1=$A$7),"")=A9))

 

[Cubist]

Another option:

Book1
	A	B	C	D	E	F
1		7/8/24	7/9/24	7/10/24	7/11/24	7/12/24
2	P1	V
3	P2	S	S
4	P3		V	V	V	R
5	P4	L	L	L	L	L
6
7	7/10/24
8	Category	Count
9	V	1
10	S
11	R
12	L	1
Sheet2

Cell Formulas
Range	Formula
B9:B12	B9	=LET(s,SUM(($B$1:$F$1=$A$7)*($B$2:$F$5=A9)),IF(s,s,""))

 

[kieran0114]

Thanks for all the help. All suggestions work and got me on my way. Appreciate the quick responses and impressed with the knowledge. Definitely will post again here next time i run into a formula issue.