# Need Help In COUNTIF Function



## Santosh080 (Jan 1, 2023)

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


PlacePlace PinName of ItemsAAA125​PENCCC254​KEYAAA146​PENTTT142​PENCILJJJ547​ERASERAAA125​PENAAA125​KEYDDD​147​GOLDDDD​147​KEY​

i need data like this,


NamePlace PinNumber of itemsAAA125​???


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 (Jan 1, 2023)

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

Book3ABCDEFGHIJ1PlacePlace PinName of ItemsPlacePlace PinNameCount2AAA125PENAAA125PEN23CCC254KEYCCC254KEY14AAA146PENAAA146PEN15TTT142PENCILTTT142PENCIL16JJJ547ERASERJJJ547ERASER17AAA125PENAAA125KEY18AAA125KEYDDDGOLD19DDDGOLD01470010147DDDKEY111DDDKEY12147Sheet1Cell FormulasRangeFormulaF2:H10F2=UNIQUE(A2:C12)J2:J10J2=COUNTIFS($A$2:$A$12,F2,B$2:$B$12,G2,C$2:$C$12,H2)Dynamic array formulas.


----------



## shift-del (Jan 1, 2023)

Hello


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


----------



## Santosh080 (Jan 1, 2023)

etaf said:


> why 3
> 
> 
> AAA  125 Pen  = 2
> ...


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 (Jan 1, 2023)

shift-del said:


> Hello
> 
> 
> ```
> ...


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


----------



## shift-del (Jan 1, 2023)

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


----------



## etaf (Jan 2, 2023)

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

Book3ABC1PlacePlace PinName of Items2AAA125PEN3CCC254KEY4AAA146PEN5TTT142PENCIL6JJJ547ERASER7AAA125PEN8AAA125KEY9DDD147GOLD10DDD147KEY11Sheet1

Book3ABCDE1PlacePlace PinNameCount2AAA12533CCC25414AAA14615TTT14216JJJ54717DDD14728009000100Sheet2Cell FormulasRangeFormulaA2:B9A2=UNIQUE(Sheet1!A2:B12)E2:E10E2=COUNTIFS(Sheet1!$A$2:$A$13,A2,Sheet1!$B$2:$B$13,B2)Dynamic array formulas.


----------

