Count If

[Sefty]

Hello,
I want to count data with criteria (N/A), if i want count PN 1 (N/A). what is wrong with this formula ?
=IF(G3:G19="PN 1";COUNTIF(H3:H19;"N/A");0)
Thanks

natriss.xlsx
	F	G	H
1
2
3		PN 1	N/A
4		PN 1	N/A
5		PN 1	1
6		PN 1	2
7		PN 1	3
8		PN 1	N/A
9		PN 1	N/A
10		PN 1	4
11		PN 1	N/A
12		PN 2	5
13		PN 2	6
14		PN 2	7
15		PN 2	N/A
16		PN 2	N/A
17		PN 2	N/A
18		PN 2	0
19		PN 2	1
20
Sheet4

 

[awoohaw]

Here are two ways:

mr excel questions 13.xlsm
	E	F	G	H
3	5		PN 1	N/A
4	5		PN 1	N/A
5			PN 1	1
6			PN 1	2
7			PN 1	3
8			PN 1	N/A
9			PN 1	N/A
10			PN 1	4
11			PN 1	N/A
12			PN 2	5
13			PN 2	6
14			PN 2	7
15			PN 2	N/A
16			PN 2	N/A
17			PN 2	N/A
18			PN 2	0
19			PN 2	1
Sheet5

Cell Formulas
Range	Formula
E3	E3	=COUNTIFS($G$3:$G$19,"PN 1",H3:H19,"N/A")
E4	E4	=SUMPRODUCT((--("PN 1"=G3:G19))*(--("N/A"=H3:H19)))

 

[Gimics]

Hey Sefty,

Are you trying to check both if column G = "PN 1" AND if column H = "N/A"?

Have you tried a =COUNTIFS formula?

=COUNTIFS(G3:G19,"PN 1",H3:H19,"N/A")

Book1
	G	H
1	5
2
3	PN 1	N/A
4	PN 1	N/A
5	PN 1	1
6	PN 1	2
7	PN 1	3
8	PN 1	N/A
9	PN 1	N/A
10	PN 1	4
11	PN 1	N/A
12	PN 2	5
13	PN 2	6
14	PN 2	7
15	PN 2	N/A
16	PN 2	N/A
17	PN 2	N/A
18	PN 2	0
19	PN 2	1
Sheet1

Cell Formulas
Range	Formula
G1	G1	=COUNTIFS(G3:G19,"PN 1",H3:H19,"N/A")

 

[duggie33]

Hi Sefty,

Try this:

=COUNTIFS(G3:G19,"PN 1",H3:H19,"N/A")

I hope that helps,

Doug

 

[Sefty]

Hi Sefty,

Try this:

=COUNTIFS(G3:G19,"PN 1",H3:H19,"N/A")

I hope that helps,

Doug

thanks for the response, very helpful

 

[Sefty]

Hey Sefty,

Are you trying to check both if column G = "PN 1" AND if column H = "N/A"?

Have you tried a =COUNTIFS formula?

=COUNTIFS(G3:G19,"PN 1",H3:H19,"N/A")

Book1
	G	H
1	5
2
3	PN 1	N/A
4	PN 1	N/A
5	PN 1	1
6	PN 1	2
7	PN 1	3
8	PN 1	N/A
9	PN 1	N/A
10	PN 1	4
11	PN 1	N/A
12	PN 2	5
13	PN 2	6
14	PN 2	7
15	PN 2	N/A
16	PN 2	N/A
17	PN 2	N/A
18	PN 2	0
19	PN 2	1
Sheet1

Cell Formulas
Range	Formula
G1	G1	=COUNTIFS(G3:G19,"PN 1",H3:H19,"N/A")

thanks for the response, very helpful