Count if with multiple condition.

[avisoft20]

[TABLE="width: 139"]
<tbody>[TR]
[TD]Dear Sir,

I want to count state wise status of billed or not billed,i am applying countifs but its not working.

Example
SHEET:1

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD="align: center"]State[/TD]
[TD="align: center"]Status[/TD]
[/TR]
[TR]
[TD]ASSAM[/TD]
[TD]BILLED[/TD]
[/TR]
[TR]
[TD]ASSAM[/TD]
[TD]NOT BILLED[/TD]
[/TR]
[TR]
[TD]ASSAM[/TD]
[TD]BILLED[/TD]
[/TR]
[TR]
[TD]CHANDIGARH[/TD]
[TD]BILLED[/TD]
[/TR]
[TR]
[TD]CHANDIGARH[/TD]
[TD]NOT BILLED[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


ANS:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]COUNT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]STATE[/TD]
[TD="align: center"]BILLED[/TD]
[TD="align: center"]NOT BILLED[/TD]
[/TR]
[TR]
[TD="align: center"]ASSAM[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]CHANDIGARH[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]



thank you
Avinash kr singh

 

[FormR]

Hi, not sure what you tried exactly but COUNTIFS() should be what you need..:

Excel 2013/2016
	A	B	C
1	STATE	BILLED	NOT BILLED
2	ASSAM	2	1
3	CHANDIGARH	1	1
Sheet2

Cell Formulas
Range	Formula
B2	=COUNTIFS(Sheet1!$A:$A,$A2,Sheet1!$B:$B,B$1)

Excel 2013/2016
	A	B
1	State	Status
2	ASSAM	BILLED
3	ASSAM	NOT BILLED
4	ASSAM	BILLED
5	CHANDIGARH	BILLED
6	CHANDIGARH	NOT BILLED
Sheet1