COUNTIF with vertical and horizontal criteria

[Jubert]

Is it possible to derive a countif using two variables - one horizontal and one vertical. Please see example below
Excel 2010

	A	B	C	D	E	F	G	H	I
2,457	2,457	2,340	944	-	-
1,507	2,919	2,780	3,058	2,919	3,058
3,360	-	-	-	-	-
1,701	1,701	1,620	1,485	-	-
2,457	2,457	2,340	2,574	2,457	1,993
2,794	2,888	2,750	3,025	2,888	3,025
1,669	3,234	3,080	3,388	3,234	3,388
234	2,415	2,300	2,530	2,415	2,530
-	454	2,160	2,376	2,268	2,376
373	560	-	-	-	-
762	2,625	2,500	2,750	2,625	2,750
793	-	-	-	-	-
-	-	-	-	-	-
Month	Person	Count
LG

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BAD80A"]Person[/TD]
[TD="bgcolor: #A5A39E, align: center"]Mar-16[/TD]
[TD="bgcolor: #A5A39E, align: center"]Apr-16[/TD]
[TD="bgcolor: #A5A39E, align: center"]May-16[/TD]
[TD="bgcolor: #A5A39E, align: center"]Jun-16[/TD]
[TD="bgcolor: #A5A39E, align: center"]Jul-16[/TD]
[TD="bgcolor: #A5A39E, align: center"]Aug-16[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFC5, align: center"]LG[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFC5, align: center"]LG[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFC5, align: center"]Ed[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFC5, align: center"]LG[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFC5, align: center"]He[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFC5, align: center"]LG[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFC5, align: center"]LG[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFC5, align: center"]CG[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFC5, align: center"]Rg[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFC5, align: center"]LG[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFC5, align: center"]Ed[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFC5, align: center"]LG[/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Apr-16[/TD]

[TD="bgcolor: #92D050, align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

I wish to put a formula in D18 which gives me a count for the number of cells >0 that are in the column denoted by cell B18 for the person denoted in cell C18.
Thanks

 

[azumi]

try this array formula

=SUM(IF((C1:H1=A18)*(B2:B13=B18)*(C2:H13<>""),1,0))

 

[wideboydixon]

Or you could use COUNTIFS, OFFSET and MATCH like this:

=COUNTIFS($B$2:$B$13, $C$18, OFFSET($B$2, 0, MATCH($B$18, $C$1:$H$1, 0), 12, 1), ">0")

WBD

 

[AlanY]

try this

=COUNTIFS($B$2:$B$13,C18,INDEX($C$2:$H$13,,MATCH(B18,$C$1:$H$1,0)),">0")

 

[Jubert]

Thanks AlanY and wideboydixon - both of those did exactly what I wanted.
(Azumi I didn't get that to work for my example).

 

[AlanY]

you're welcome