Countifs / sumproducts to count value based on column header

[p4nny]

	06/10/2017	29/09/2017	22/09/2017
BA1	y	y
BA2	Y		y
BA3	y	y
BA4			y

<tbody>
</tbody>

Hi,

This is an example of how my dataset looks.

I'm looking to count the number of "Y"'s in any specified week so if I type 22/09/2017 in a cell, it would count the number of "Y"'s in that column

Much appreciated

Many thanks in advance,

 

[Canapone]

Hi

B1:D1 houses the dates, in W1 = 22/09/2017


=SUMPRODUCT((B2:D100="Y")*(B1:D1=W1))

----

If you'd need to add another condition (example: "Y" per "BA3" under date in W1):

=SUMPRODUCT((B2:D100="Y")*(B1:D1=W1)*(A2:A100="BA3"))


Regards

 

[p4nny]

Great thank you..

Rather than "Y", if I was have a criteria of "DOES NOT EQUAL BLANK", how would I do that?

Many thanks again

 

[Canapone]

Hi again

same data layout

=SUMPRODUCT((B2:D100<>"")*(B1:D1=W1))

=SUMPRODUCT(NOT(ISBLANK(B2:D100))*(B1:D1=W1))

Regards