using count if function

[aryanaveen]

Hi All,

I am looking for a formula where I can count based on 2 criteria.

Example
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Naveen[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Praveen[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]f[/TD]
[TD]f[/TD]
[TD]f[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Abhay[/TD]
[TD]d[/TD]
[TD]d[/TD]
[TD]g[/TD]
[TD]g[/TD]
[TD]g[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Prakash[/TD]
[TD]d[/TD]
[TD]f[/TD]
[TD]i[/TD]
[TD]a[/TD]
[TD]i[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Giri[/TD]
[TD]d[/TD]
[TD]j[/TD]
[TD]f[/TD]
[TD]j[/TD]
[TD]k[/TD]
[TD]a[/TD]
[/TR]
</tbody>[/TABLE]

In the above table I want to count number of "A" in range "C1:H5" where Column B is having "Naveen". Please note "Naveen" can be in any row under column B.


Please help

 

[FormR]

Please note "Naveen" can be in any row under column B.

Hi, if Naveen can only appear once in column B then you can try this:

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1	A	B	C	D	E	F	G	H		Naveen
2	1	Naveen	A	B	A	A	C	D		3
3	2	Praveen	C	D	f	f	f	f
4	3	Abhay	d	d	g	g	g	f
5	4	Prakash	d	f	i	a	i	a
6	5	Giri	d	j	f	j	k	a
Sheet1

Cell Formulas
Range	Formula
J2	=COUNTIFS(INDEX($C$2:$H$6,MATCH(J1,$B$2:$B$6,0),0),"A")

 

[sparky2205]

Have a look at this:
=SUMPRODUCT(--(B1:B5="Naveen"),--EXACT(C1:C5,"A"))
This is case sensitive for "A"

 

[nemmi69]

Try

=COUNTIFS($B$1:$B$5,"Naveen",$C$1:$C$5,"A")+COUNTIFS($B$1:$B$5,"Naveen",$D$1:$D$5,"A")+COUNTIFS($B$1:$B$5,"Naveen",$E$1:$E$5,"A")+COUNTIFS($B$1:$B$5,"Naveen",$F$1:$F$5,"A")+COUNTIFS($B$1:$B$5,"Naveen",$G$1:$G$5,"A")+COUNTIFS($B$1:$B$5,"Naveen",$H$1:$H$5,"A")