using count if function

[aryanaveen]

Hi All,

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

Example

A	B	C	D	E	F	G	H
1	Naveen	A	B	A	A	C	D
2	Praveen	C	D	f	f	f	f
3	Abhay	d	d	g	g	g	f
4	Prakash	d	f	i	a	i	a
5	Giri	d	j	f	j	k	a

<tbody>
</tbody>

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")