Counting Non Empty ROWS based off of multiple criteria

[spoloms1]

I would like to count the non empty rows that meet specific criteria.

I have a very simplified version of my data in the table below (my table is much larger and is confined to excel). I can count the non empty cells that meet multiple criteria by using a sumproduct formula:
sumproduct((B1:F1=2014)*(A2:A6=”Yes”)*(B2:F6>0))

What I would like to do is count the non empty rows where certain criteria is met in row 1 and column A. So the count of the rows that satisfy Year = 2014 (row 1) and Criteria = Yes (column A) should produce 3. I need to count the rows once so I am not double counting when I aggregate the years.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Criteria[/TD]
[TD]2014[/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[TD]2015[/TD]
[TD]2016[/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[dreid1011]

=COUNTIF(E13:E17,"=yes")+COUNTIF(F12:J12,"=2014")

Adjust references to suit your data. Let me know if this is what you want.


Edit: Nvm, I have reread your post and I understand what you are looking for now. I will adjust the formula.

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][/tr]
[tr][td]

1​

[/td][td]Criteria[/td][td]

2014​

[/td][td]

2014​

[/td][td]

2015​

[/td][td]

2015​

[/td][td]

2016​

[/td][td][/td][td][/td][td]

2014​

[/td][/tr]

[tr][td]

2​

[/td][td]Yes[/td][td]

4​

[/td][td]

2​

[/td][td][/td][td]

3​

[/td][td][/td][td][/td][td]Yes[/td][td]

3​

[/td][/tr]

[tr][td]

3​

[/td][td]Yes[/td][td][/td][td]

3​

[/td][td]

8​

[/td][td]

8​

[/td][td]

2​

[/td][td][/td][td]No[/td][td]

2​

[/td][/tr]

[tr][td]

4​

[/td][td]No[/td][td]

3​

[/td][td][/td][td]

6​

[/td][td][/td][td]

4​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]No[/td][td]

5​

[/td][td]

7​

[/td][td][/td][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]Yes[/td][td]

1​

[/td][td]

1​

[/td][td]

3​

[/td][td][/td][td]

3​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

I2, control+shift+enter, not just enter, and copy down:

=SUM(IF(MMULT(($A$2:$A$6=$H2)*($B$1:$F$1=I$1)*ISNUMBER($B$2:$F$6),
    TRANSPOSE(COLUMN($B$1:$F$6)^0))>0,1))

 

[spoloms1]

Aladin - this works beautifully - thank you!

 

[Aladin Akyurek]

Aladin - this works beautifully - thank you!

You are welcome.