Combining Lookup and CountIFS

[Dhinakaran]

Hello,

Need your help on combining Lookup and CountIFs for the below data where in the report I need to display the count of Payout types against the product category.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Month	Payout	Product Category	Value
2	May	Debt	Fruits	827874				Report
3	May	Crdt	Fruits	804099				Product	Count of Debt	Count of Crdt
4	May	Crdt	Fruits	678621				Fruits
5	May	Debt	Groceries	858400				Groceries
6	May	Crdt	Groceries	731474				Vegetables
7	May	Crdt	Vegetables	679994
8	May	Debt	Vegetables	792585
9	May	Crdt	Vegetables	867564
10
11
12
13
14
15
Sheet7

TIA

 

[hagia_sofia]

If you change your headers to Debt and Crdt, then in 365 =COUNTIFS(B2:B9;I3:J3;C2:C9;H4:H6)

 

[Cubist]

Consider pivot table as well.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Month	Payout	Product Category	Value
2	May	Debt	Fruits	827874				Report
3	May	Crdt	Fruits	804099				Product	Count of Debt	Count of Crdt
4	May	Crdt	Fruits	678621				Fruits	1	2
5	May	Debt	Groceries	858400				Groceries	1	1
6	May	Crdt	Groceries	731474				Vegetables	1	2
7	May	Crdt	Vegetables	679994
8	May	Debt	Vegetables	792585
9	May	Crdt	Vegetables	867564
Sheet3

Cell Formulas
Range	Formula
I4:I6	I4	=COUNTIFS($B$2:$B$9,"Debt",$C$2:$C$9,$H4:$H6)
J4:J6	J4	=COUNTIFS($B$2:$B$9,"Crdt",$C$2:$C$9,$H4:$H6)
Dynamic array formulas.