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.

Criteria	2014	2014	2015	2015	2016
Yes	4	2		3
Yes		3	8	8	2
No	3		6		4
No	5	7		1
Yes	1	1	3		3

<tbody>
</tbody>

 

[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]

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Criteria	2014​	2014​	2015​	2015​	2016​			2014​
2​	Yes	4​	2​		3​			Yes	3​
3​	Yes		3​	8​	8​	2​		No	2​
4​	No	3​		6​		4​
5​	No	5​	7​		1​
6​	Yes	1​	1​	3​		3​

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.