Countifs / sumproducts to count value based on column header

[p4nny]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]06/10/2017[/TD]
[TD]29/09/2017[/TD]
[TD]22/09/2017[/TD]
[/TR]
[TR]
[TD]BA1[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BA2[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]BA3[/TD]
[TD]y[/TD]
[TD]y[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BA4[/TD]
[TD][/TD]
[TD][/TD]
[TD]y[/TD]
[/TR]
</tbody>[/TABLE]


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