Need Help In COUNTIF Function

[Santosh080]

hello friends
Need help with count function. I have a excel file data like this,

Place	Place Pin	Name of Items
AAA	125​	PEN
CCC	254​	KEY
AAA	146​	PEN
TTT	142​	PENCIL
JJJ	547​	ERASER
AAA	125​	PEN
AAA	125​	KEY
DDD	​ 147​	GOLD
DDD	​ 147​	KEY​

i need data like this,

Name	Place Pin	Number of items
AAA	125​	???

In "Number of item" i want number of items that must match "Name", "Place Pin" and " Name of Item".

Like i want to count where Place =AAA, Place Pin=125, Name Item =Pen and Key. Here the result will be 3.

please help.

 

[etaf]

why 3

must match "Name", "Place Pin" and " Name of Item".

AAA 125 Pen = 2
AAA 146 Pen = 1

if you want 3 - then are you ignoring the Place PIN

COUNTIFS()

Book3
	A	B	C	D	E	F	G	H	I	J
1	Place	Place Pin	Name of Items			Place	Place Pin	Name		Count
2	AAA	125	PEN			AAA	125	PEN		2
3	CCC	254	KEY			CCC	254	KEY		1
4	AAA	146	PEN			AAA	146	PEN		1
5	TTT	142	PENCIL			TTT	142	PENCIL		1
6	JJJ	547	ERASER			JJJ	547	ERASER		1
7	AAA	125	PEN			AAA	125	KEY		1
8	AAA	125	KEY			DDD		GOLD		1
9	DDD		GOLD			0	147	0		0
10		147				DDD		KEY		1
11	DDD		KEY
12		147
Sheet1

Cell Formulas
Range	Formula
F2:H10	F2	=UNIQUE(A2:C12)
J2:J10	J2	=COUNTIFS($A$2:$A$12,F2,B$2:$B$12,G2,C$2:$C$12,H2)
Dynamic array formulas.

 

[shift-del]

Hello

=SUM(COUNTIFS(Table1[Place],"AAA",Table1[Place Pin],125,Table1[Name of Items],{"Pen","Key"}))

 

[Santosh080]

why 3


AAA 125 Pen = 2
AAA 146 Pen = 1

if you want 3 - then are you ignoring the Place PIN

COUNTIFS()

Book3
	A	B	C	D	E	F	G	H	I	J
1	Place	Place Pin	Name of Items			Place	Place Pin	Name		Count
2	AAA	125	PEN			AAA	125	PEN		2
3	CCC	254	KEY			CCC	254	KEY		1
4	AAA	146	PEN			AAA	146	PEN		1
5	TTT	142	PENCIL			TTT	142	PENCIL		1
6	JJJ	547	ERASER			JJJ	547	ERASER		1
7	AAA	125	PEN			AAA	125	KEY		1
8	AAA	125	KEY			DDD		GOLD		1
9	DDD		GOLD			0	147	0		0
10		147				DDD		KEY		1
11	DDD		KEY
12		147
Sheet1

Cell Formulas
Range	Formula
F2:H10	F2	=UNIQUE(A2:C12)
J2:J10	J2	=COUNTIFS($A$2:$A$12,F2,B$2:$B$12,G2,C$2:$C$12,H2)
Dynamic array formulas.

There are some conditions like Place=AAA, PIN=125 and Items are Pen & Key. I want Number of items if all conditions satisfy. Here in above example the result is three 3 because

AAA 125 PEN
AAA 125 PEN
AAA 125 KEY


Here i do not want Pin= 146


I want the count result in another sheet like i mentioned above not side of the data sheet.

 

[Santosh080]

Hello

=SUM(COUNTIFS(Table1[Place],"AAA",Table1[Place Pin],125,Table1[Name of Items],{"Pen","Key"}))

thanks, i have tried it but why it return zero as count result.

 

[shift-del]

I don't know. In my worksheet it counts to three.
Try to enter the formula with CTRL+SHIFT+ENTER.

 

[etaf]

can you give some expected results .....
OR is it ONLY AAA 125 you want a count for

I put at side for easy display, pretty easy to add to another sheet

here it is in a separate sheet

This time just counting the place and place pin

Book3
	A	B	C
1	Place	Place Pin	Name of Items
2	AAA	125	PEN
3	CCC	254	KEY
4	AAA	146	PEN
5	TTT	142	PENCIL
6	JJJ	547	ERASER
7	AAA	125	PEN
8	AAA	125	KEY
9	DDD	147	GOLD
10	DDD	147	KEY
11
Sheet1

Book3
	A	B	C	D	E
1	Place	Place Pin	Name		Count
2	AAA	125			3
3	CCC	254			1
4	AAA	146			1
5	TTT	142			1
6	JJJ	547			1
7	DDD	147			2
8	0				0
9	0	0			0
10					0
Sheet2

Cell Formulas
Range	Formula
A2:B9	A2	=UNIQUE(Sheet1!A2:B12)
E2:E10	E2	=COUNTIFS(Sheet1!$A$2:$A$13,A2,Sheet1!$B$2:$B$13,B2)
Dynamic array formulas.